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.
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.