IIf and parameter query

A

Amy Blankenship

Hello, all;

I am trying to return just the records for a given course if the course id
is given in the parameter of a parameter query, or all if the course id
given is 0.

So in the Where part of the query builder, I'm using:

IIF ([Enter CID] > 0, [Enter CID], "'*'")

I get the error "This expression is typed incorrectly, or is too complex to
be evaluated." I've searched on google and seen this problem mentioned
several times, but never a resolution that would work for my problem.

I've also tried

IIF ([Enter CID] > 0, [Enter CID], "LIKE '*'")

IIF ([Enter CID] > 0, [Enter CID], "*")

IIF ([Enter CID] > 0, [Enter CID], ">0'")

IIF ([Enter CID] > 0, [Enter CID], "IS NOT NULL")

Just for grins,

IIF ([Enter CID] > 0, [Enter CID], 1)`works, but obviously does not give the
right results.

Can anyone explain to me why this is happening? If I could see it from
Access' perspective, maybe I could work out a solution.

Thanks;

Amy
 
F

fredg

Hello, all;

I am trying to return just the records for a given course if the course id
is given in the parameter of a parameter query, or all if the course id
given is 0.

So in the Where part of the query builder, I'm using:

IIF ([Enter CID] > 0, [Enter CID], "'*'")

I get the error "This expression is typed incorrectly, or is too complex to
be evaluated." I've searched on google and seen this problem mentioned
several times, but never a resolution that would work for my problem.

I've also tried

IIF ([Enter CID] > 0, [Enter CID], "LIKE '*'")

IIF ([Enter CID] > 0, [Enter CID], "*")

IIF ([Enter CID] > 0, [Enter CID], ">0'")

IIF ([Enter CID] > 0, [Enter CID], "IS NOT NULL")

Just for grins,

IIF ([Enter CID] > 0, [Enter CID], 1)`works, but obviously does not give the
right results.

Can anyone explain to me why this is happening? If I could see it from
Access' perspective, maybe I could work out a solution.

Thanks;

Amy
Click to expand...

If you are going to use the * wildcard, you need to also use the Like
keyword.

Like IIf([Enter CID] = 0,"*",[Enter CID])

You cculd also simply leave the parameter prompt empty.
Like IIf(IsNull([Enter CID]),"*",[Enter CID])
 
A

Amy Blankenship

Thanks a lot! this worked perfectly.

-Amy

fredg said:
Hello, all;

I am trying to return just the records for a given course if the course
id
is given in the parameter of a parameter query, or all if the course id
given is 0.

So in the Where part of the query builder, I'm using:

IIF ([Enter CID] > 0, [Enter CID], "'*'")

I get the error "This expression is typed incorrectly, or is too complex
to
be evaluated." I've searched on google and seen this problem mentioned
several times, but never a resolution that would work for my problem.

I've also tried

IIF ([Enter CID] > 0, [Enter CID], "LIKE '*'")

IIF ([Enter CID] > 0, [Enter CID], "*")

IIF ([Enter CID] > 0, [Enter CID], ">0'")

IIF ([Enter CID] > 0, [Enter CID], "IS NOT NULL")

Just for grins,

IIF ([Enter CID] > 0, [Enter CID], 1)`works, but obviously does not give
the
right results.

Can anyone explain to me why this is happening? If I could see it from
Access' perspective, maybe I could work out a solution.

Thanks;

Amy
Click to expand...

If you are going to use the * wildcard, you need to also use the Like
keyword.

Like IIf([Enter CID] = 0,"*",[Enter CID])

You cculd also simply leave the parameter prompt empty.
Like IIf(IsNull([Enter CID]),"*",[Enter CID])
Click to expand...
 
Top