iif statement in query criteria

[jillrein]

Can you help, I am trying to use the following criteria in a query

IIf([Forms]![StudentAttendance]![Combo0]<>0,[Forms]![StudentAttendance]![Combo0],Like "*")

I have a form with a combo box giving the user the option to select a value, otherwise I want to show all records. the bit that doesn't work is the false condition, Like "*"

 

[boblarson]

Try changing to:


IIf([Forms]![StudentAttendance]![Combo0]<>0,[Forms]![StudentAttendance]![Combo0],"Like '*'")

 

[jillrein]

I tried that but now it returns an error if I do not put in a value. says expression is typed incorrectly or is too complex. Do you have any other ideas?

 

[Brianwarnock]

Read This
Brian

 

[boblarson]

Read This
Brian

I was thinking of that. I am still trying to get that one memorized as it is extremely useful. One of these days I might actually remember the syntax without having to look

 

[Brianwarnock]

I always post the link so that the user gets a full explanation, if they are prepared to read.
Jon explains it better than I would.

Brian

 

[jillrein]

Thanks - this looks like a great solution. Just have to read it again to understand it properly.

 

[Brianwarnock]

Its actually easier to understand after you have done it and looked at the SQL.

Brian

 

[jardiamj]

I have used the solution for this posted by Jon K, but actually when I have to handle multiple search fields and I want to allow the user to leave them blank to show all the records I rather build the SQL expression by code and pass it to the Qry. By this way I can trap the null value if the box was left blank before passing the criteria to the WHERE clause and simply not to use it.
I have also done search boxes that allow the user to show individual selections or range of records by using coma separator like (3,5,8) or a dash in the case of it being a range like (1-7). I will look for this code in my projects and will write a tutorial when I have time, so it can be useful for someone else.
Have a nice day. Cheers!

 

[jillrein]

Thanks everyone for the help. Brian, I have used the solution you proposed. It all works now and I understand what it does so much appreciated. Will look forward to seeing the tutorial if and when it is available.

 

[Vindicator]

Could you please repost that Link? I get a no match found page appear when I click on it. Cheers

 

[Brianwarnock]

here

I' had the same problem, the ref seems to change! I'll ask about that.

I do an advanced search
keywords - Query by form

By Jon k
Threads started by
in Access reference

Brian

 

[wiklendt]

Read This
Brian

tried that link (and the other one too):

Sorry - no matches. Please try some different terms.

was the issue resolved with

"like & '*'"

?

 

[jillrein]

Unfortunately I can't find the article again but if it helps at all here is my query based on what I learned in the article. It resolved my issue.

SELECT register.classID
FROM register
WHERE ((([register]![ClassID]=[Forms]![Statt]![cboclass] Or [Forms]![Statt]![cboclass] Is Null)=True));

 

[Brianwarnock]

I have taken some advice and now know that the correct address should be


http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian