Using IIF statements in queries (1 Viewer)

Hi all, fairly new to access and I'm trying to create a 'Search' form.
The form enables people to search a combination of 20 different criteria. I have been able to get the 'text' searches to work and am using the following in the criteria section for each text field searchable:

Is Null Or Like "*" & [Forms]![frmSearch]![Rate_Payer] & "*"

I have also set up the field as shown below to avoid the query returning blank records.

Ratepayer: Nz([tblFOIData]![Ratepayer],"")

This is working great for all the text field searches, the problem arises when I search for numbers or date fields as I want the user to be able to select if they want these dates to be before, after, equal or between and numbers to be less than, greater than, equal to or between.

I have set up a combo box on my form with these selections (before, after, equal, between) and when the user selects the option, that assigned text box is visible and the others are hidden, enabling the user to enter their search. My plan is to then use the IIF statement to see what, if any, of these textboxes contain information and if they do to run the search based on that. This is what I've tried so far but am having no luck.

IIf([Forms]![frmSearch]![Before_Start_Date]<>"",([tblFOIData].[Start_Date_of_Liability]) Between #01/01/1900# And [Forms]![frmSearch]![Before_Start_Date],IIf([Forms]![frmSearch]![After_Start_Date]<>"",([tblFOIData].[Start_Date_of_Liability])>[Forms]![frmSearch]![After_Start_Date],IIf([Forms]![frmSearch]![Equals_Start_Date]<>"",([tblFOIData].[Start_Date_of_Liability])=[Forms]![frmSearch]![Equals_Start_Date],IIf([Forms]![frmSearch]![Between_Start_Date_1]<>"",([tblFOIData].[Start_Date_of_Liability]) Between [Forms]![frmSearch]![Between_Start_Date_1] And [Forms]![frmSearch]![Between_Start_Date_2],([tblFOIData].[Start_Date_of_Liability]) Is Null))))


Any help would be really appreciated as this is starting to drive me insane!!!:banghead:

Thanks in advance.

you should really AVOID using IIFs in queries. Ok, 1, maybe 2, but if you start nesting them, then you need either:
join the main data to a lookup table to the substitution
or
build a custom function to do the same.

usage in query:

IsValidCode([fld1],[fld2])

Agree with Ran, you only get to nest 2 functions inline. More than that and its time to build a function.

However, I believe your entire methodology is incorrect for your issue. A query shouldn't be the workhorse of this, a filter should be. How are the results of the search being delivered to the user?

If its the form they are on then you would use the Form.Filter property (https://msdn.microsoft.com/en-us/vba/access-vba/articles/form-filter-property-access)

If its a new form that opens up and shows them you would use a DoCmd.OpenForm (https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openform-method-access).

If its a report that opens up you would use a DoCmd.OpenReport (https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access)

The general idea in all those methods is that you construct a filter string from the user supplied inputs and then open the form/report to just the records they want to see. That would be the best way to accomplish the application of criteria and not through a query.

Thanks for the advice plog and Ranman. If I were to use the code you have provided Ranman, how would I actually go about getting it to work?!

Sorry guys I'm a beginner with Access and have been trying to pick it up myself through bits and bobs online, hence the lack of best practice!!

sorry to throw you in the deep end, but this requires some vb.
using the example,
each line in the CASE , would have the condition you want to test.
the line under it is the result you want.

the last line MUST be the assignment to the function name to get the result (in the example):

IsValidCode = vMsg
end function

in the vbe (alt-F11)
insert a new module,
then insert the example code,
then modify the code to fit your needs.

sorry , but this is advanced.