Using IIF in query to supply criteria based on input from user 1

[1DMF]

Hello,

I'm not sure if this is possible, I certainly hope so, so here goes...

I have a report, attached to a query, it requires user input for one of the match criteria.

However, I don't want to have the user input the full and correct syntax, is it possible to supply the criteria via an IIF based on user input

...here is the senario...

field = ContactTypeID

there are many types for this field (Prospect, Member, X-Member, Withdrawn)

I want to ask the user for input but only give them the option "Member" or "*" for all (well actualy only current members and x-members).

If * is entered no records are found, So I thought I'd use an IIF to supply the criteria based on user input like so..

IIf([Please enter either Member or *]="*","Member Or X-Member","Member")

So on the Criteria section in design view on the query I place that IIF, it correctly asks for input and if I enter 'Member' it supplies me all members as expected, yet if I type *, no records are selected.

Is it possible, and if so where am I going wrong.

Many thanks
1DMF


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

ok I got a workround of sorts, but would still like to know if you can use OR within the IIF.

my workround was simple for this occasion, as I always want Member, but may also want X-Member i placed the following on the criteria.

"Member" Or IIf([Please enter Membership Type (either M=Member or *=All)]="*","X-Member","Member")

so basically if the user puts anything other than * it looks for

"Member" Or "Member"

otherwise it looks for

"Member" Or "X-Member"

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ddiamond]

If you were doing this in VBA and dynamically creating your query, your first method would have worked. If you want to do it strictly with a pre-defined query, then your work-around is the only way to do it. I personally think your work-away is the simplist way to accomplish this. Anything else would involve a bit more code with little benifit.

 

[1DMF]

hmmm, I was hoping for a solution, as the person I was designing the report for has thrown a spanner in the works wanting...

Member, X-Member, Or Both

now making it a 3 pronged IIF, any got any suggestion without me writing some VBA code ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Golom]

I think your original code needs to be

IIf([Please enter either Member or *]="*",
"Member" Or "X-Member","Member")

As it is, the test is for the string "Member OR X-Member". The "OR" is just part of the string and not a logical operator.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[1DMF]

Thanks for the reply but that doesn't work, as it breaks the "quotes" ande causes an error, I worked the syntax out in the end, it's rather ugly but it works fine

IIf([Please enter Membership Type (either M=Member, X=X-Member or B=Both)]="X","X-Member",IIf([Please enter Membership Type (either M=Member, X=X-Member or B=Both)]="M","Member","Member")) Or IIf([Please enter Membership Type (either M=Member, X=X-Member or B=Both)]="B","X-Member","")

it also defaults to 'Member' if invalid option is chosen or the enter key is pressed.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ddiamond]

would this work?

like [Please enter either Member, X-Member or *]

 

[1DMF]

no because "*" doesn't work, I thought it might but it doesn't, not sure if you have to use the 'like' operator to make it work.

I'm happy with the IIF i got working

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[ddiamond]

Yes, you need to use the like operator to make it work.

 

[1DMF]

d'oh - i should have read your post properly, just noticed you had used 'like'.

thanks for your help

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.