Iif with true part containing 2 values

[hardyd44]

First Post - please be gentle

I may be missing something here, but can any one help?

I am using a Iif statment in a query to filter records as follows:

If([value from a form]= "all holidays",("weeks hols" or "days hols"),"not a holiday")

This is returning a to complex to calculate error - I think it is a Syntax error but could be wrong
but if I enter The string "week hols" or "days Hol" as the criteria without the Iif statment that works fine.

Thanks Dean

 

[Rich]

How can you have two answers?

 

[hardyd44]

2 answers

What I have is a list of Absence types - Week Hol, Day Hol, Sick, Training, Etc, Etc, - What I am trying to achieve is If the "All Holidays" is selected the records with either "week hol" or "Day Hol" will be selected or it can be filtered by "week Hol" or "Day Hol" singulary from the same form

I hope this makes sense

 

[Brianwarnock]

If you haven't sorted this out I would try a different approach. In the criteria of the query put
Like "*" & [Forms]![formname]![textboxname]
then to ask for all holidays just put hols, to ask for anything else put it in full.

Brian

 

[neileg]

If you have two conditions that must be true, use an AND() statement. You are putting your second criteria in the 'value if true' section of the IIF statement which will not give you what you want. Probably will look this this, but untested by me.

Iif(AND([value from a form]= "all holidays",OR("weeks hols","days hols")),"not a holiday","")

Sounds like a horrific design, however.

 

[hardyd44]

Guys,

Thanks for your help but neither method did what I was after - so attacked the problem from a different angle and used my very scant knowledge of VB to do the task and eventually got there but narrowed the choices first - but thank you for everybodies help anyway

Dean