IIF and like "*"

[C.Y.]

I have a query that uses a criteria like the following:

Select custName, custID, ItemNumber, price from tblHistory
having custID In (select custID from tblCustFlt)

If there is something in the tblCustFlt table, this query
works wonderfully. But I want to allow a clause that if
the table is empty, make the criteria Like "*" instead.
Problem is the Like. I can't find a way for the criteria
to take a Like "*" when combined with an expression. I
always try to test some theories with easy steps, so if
the following statements look basic it is on purpose.

I have tried iif([forms]![MainForm]![txtField]
=0,"20",Like "*"). The true statment works, but I can't
get the False Like statement to work.

I have also tried programmatically using a function to
build the statement. Used returnValue() as the criteria
where the returnValue function returned Like "*". No luck
here either.

I have managed to get this to work building the entire
query using code, problem is it becomes complicated very
quick and I like to keep code simple so that others can
manage it without tearing their hair out.

Bottom line, has anyone done this before or am I making
this too complicated to begin with.

Thank you for your advice
C.Y.

 

[C.Y.]

-----Original Message-----
I have a query that uses a criteria like the following:

Select custName, custID, ItemNumber, price from tblHistory
having custID In (select custID from tblCustFlt)

If there is something in the tblCustFlt table, this query
works wonderfully. But I want to allow a clause that if
the table is empty, make the criteria Like "*" instead.
Problem is the Like. I can't find a way for the criteria
to take a Like "*" when combined with an expression. I
always try to test some theories with easy steps, so if
the following statements look basic it is on purpose.

I have tried iif([forms]![MainForm]![txtField]
=0,"20",Like "*"). The true statment works, but I can't
get the False Like statement to work.

I have also tried programmatically using a function to
build the statement. Used returnValue() as the criteria
where the returnValue function returned Like "*". No luck
here either.

I have managed to get this to work building the entire
query using code, problem is it becomes complicated very
quick and I like to keep code simple so that others can
manage it without tearing their hair out.

Bottom line, has anyone done this before or am I making
this too complicated to begin with.

Thank you for your advice
C.Y.
.

In the iif statement listed above:iif([forms]![MainForm]!
[txtField]=0,"20",Like "*") I have learned that instead
of using the Like "*", just use name of the field the
criteria was used against. ie if the field was CustID the
iif statement would look like:

iif([forms]![MainForm]![txtField]=0,"20",

.
[custID]). Which tells it to use all in custID.

So, got it to work in this example. Tried combining it
with the In() statement and again, I am having no luck.
IIF statement looks like

iif([forms]![MainForm]![txtField]=0,

.[custID] In
(select custID from tblCustFlt,

.[custID]).

Obviously, I am still working through this, but I come
back to this note frequently in case anyone has any
suggestions.

Appreciate it,
CY

 

[C.Y.]

-----Original Message-----
I have a query that uses a criteria like the following:

Select custName, custID, ItemNumber, price from tblHistory
having custID In (select custID from tblCustFlt)

If there is something in the tblCustFlt table, this query
works wonderfully. But I want to allow a clause that if
the table is empty, make the criteria Like "*" instead.
Problem is the Like. I can't find a way for the criteria
to take a Like "*" when combined with an expression. I
always try to test some theories with easy steps, so if
the following statements look basic it is on purpose.

I have tried iif([forms]![MainForm]![txtField]
=0,"20",Like "*"). The true statment works, but I can't
get the False Like statement to work.

I have also tried programmatically using a function to
build the statement. Used returnValue() as the criteria
where the returnValue function returned Like "*". No luck
here either.

I have managed to get this to work building the entire
query using code, problem is it becomes complicated very
quick and I like to keep code simple so that others can
manage it without tearing their hair out.

Bottom line, has anyone done this before or am I making
this too complicated to begin with.

Thank you for your advice
C.Y.
.

In the iif statement listed above:iif([forms]![MainForm]!
[txtField]=0,"20",Like "*") I have learned that instead
of using the Like "*", just use name of the field the
criteria was used against. ie if the field was CustID the
iif statement would look like:

iif([forms]![MainForm]![txtField]=0,"20",

.
[custID]). Which tells it to use all in custID.

So, got it to work in this example. Tried combining it
with the In() statement and again, I am having no luck.
IIF statement looks like

iif([forms]![MainForm]![txtField]=0,

.[custID] In
(select custID from tblCustFlt,

.[custID]).

Obviously, I am still working through this, but I come
back to this note frequently in case anyone has any
suggestions.

Appreciate it,
CY