Query Problem on multiple criteria search (1 Viewer)

jufg

New member
Local time
Today, 10:02
Joined
Nov 3, 2017
Messages
3
Hi all, I'm back working on my multi-search form for users, where the user can enter in details and also have the ability to search between dates and numerical values.

The problem, is that the query is too complex. When I run the query with just the text searches it works fine, the issue arises when I try and search with my 'between' values. Below is the SQL code.

Code:
SELECT *
FROM tblFOIData

WHERE (((tblFOIData.Area_Ref) Is Null Or (tblFOIData.Area_Ref) Like "*" & [Forms]![frmSearch]![cmbBilling_Authority] & "*") 
AND ((Nz([tblFOIData]![Ratepayer],"")) Is Null Or (Nz([tblFOIData]![Ratepayer],"")) Like "*" & [Forms]![frmSearch]![Rate_Payer] & "*") 
AND ((tblFOIData.Scheme_Reference) Is Null Or (tblFOIData.Scheme_Reference) Like "*" & [Forms]![frmSearch]![Scheme_Ref] & "*") 
AND ((tblFOIData.Account_Mailing_Address) Is Null Or (tblFOIData.Account_Mailing_Address) Like "*" & [Forms]![frmSearch]![Account_Mailing] & "*") 
AND ((tblFOIData.Empty_Prop) Is Null Or (tblFOIData.Empty_Prop) Like "*" & [Forms]![frmSearch]![Empt_Prop] & "*") 
AND ((tblFOIData.Charity) Is Null Or (tblFOIData.Charity) Like "*" & [Forms]![frmSearch]![Charity] & "*") 
AND ((tblFOIData.SBRR) Is Null Or (tblFOIData.SBRR) Like "*" & [Forms]![frmSearch]![SBRR] & "*") 
AND ((tblFOIData.Address) Is Null Or (tblFOIData.Address) Like "*" & [Forms]![frmSearch]![Address] & "*") 
AND ((tblFOIData.Postcode) Is Null Or (tblFOIData.Postcode) Like "*" & [Forms]![frmSearch]![Postcode] & "*") 
AND ((tblFOIData.Description) Is Null Or (tblFOIData.Description) Like "*" & [Forms]![frmSearch]![Description] & "*")

AND ((tblFOIData.Start_Date_of_Liability) Is Null Or (tblFOIData.Start_Date_of_Liability) Between [Forms]![frmSearch]![Between_Start_Date_1] And [Forms]![frmSearch]![Between_Start_Date_2]) 
AND ((tblFOIData.[2017_RV]) Is Null Or (tblFOIData.[2017_RV]) Between [Forms]![frmSearch]![Between_2017_RV_1] And [Forms]![frmSearch]![Between_2017_RV_2]) 
AND ((tblFOIData.[2017_Effective_From]) Is Null Or (tblFOIData.[2017_Effective_From]) Between [Forms]![frmSearch]![Between_2017_Effective_1] And [Forms]![frmSearch]![Between_2017_Effective_2]) 
AND ((tblFOIData.[2017_Alteration_Date]) Is Null Or (tblFOIData.[2017_Alteration_Date]) Between [Forms]![frmSearch]![Between_2017_Alteration_1] And [Forms]![frmSearch]![Between_2017_Alteration_2]) 
AND ((tblFOIData.Compiled_2017_RV) Is Null Or (tblFOIData.Compiled_2017_RV) Between [Forms]![frmSearch]![Between_Compiled_RV_1] And [Forms]![frmSearch]![Between_Compiled_RV_2]) 
AND ((tblFOIData.Percentage_Increase) Is Null Or (tblFOIData.Percentage_Increase) Between [Forms]![frmSearch]![Between_Percentage_Increase_1] And [Forms]![frmSearch]![Between_Percentage_Increase_2]) 
AND ((tblFOIData.[2010_Rateable_Value]) Is Null Or (tblFOIData.[2010_Rateable_Value]) Between [Forms]![frmSearch]![Between_2010_RV_1] And [Forms]![frmSearch]![Between_2010_RV_2]) 
AND ((tblFOIData.[2017_UAR_Current]) Is Null Or (tblFOIData.[2017_UAR_Current]) Between [Forms]![frmSearch]![Between_UAR_Current_1] And [Forms]![frmSearch]![Between_UAR_Current_2]) 
AND ((tblFOIData.[2017_UAR_Historic]) Is Null Or (tblFOIData.[2017_UAR_Historic]) Between [Forms]![frmSearch]![Between_UAR_Historic_1] And [Forms]![frmSearch]![Between_UAR_Historic_2]));


The user enters all this data on a form and for the 'between' values the user selects from a dropdown menu to state that they are using that criteria. What I am hoping to do is bypass the criteria when the user doesn't click on 'use criteria' in the dropdown menu. Is this possible using an if statement or Case??

Any help would be greatly appreciated
 

Users who are viewing this thread

Top Bottom