Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-06-2017, 05:26 AM   #1
jufg
Newly Registered User
 
Join Date: Nov 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jufg is on a distinguished road
Query Problem on multiple criteria search

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

jufg is offline   Reply With Quote
Old 11-06-2017, 07:44 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,193
Thanks: 10
Thanked 3,893 Times in 3,836 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query Problem on multiple criteria search

My personal preference when there are several optional search criteria is dynamic SQL, demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-06-2017, 07:50 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,992
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Query Problem on multiple criteria search

A well-endowed, good looking genius gave you some good advice on your other post as how to accomplish this:

https://www.access-programmers.co.uk...d.php?t=296768

In short, don't this via a query. Instead do it by opening a form/report to just the records you want to display.

plog is offline   Reply With Quote
Reply

Tags
access , query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
turning single freetext on change search to a multiple criteria search splreece General 1 12-19-2016 09:34 AM
Multiple word search criteria in a query ramez75 Queries 45 02-03-2013 07:20 AM
Query that would search multiple criteria and delete justinwright Queries 12 08-25-2010 08:05 AM
Criteria Multiple Search Fields IN Query natural Queries 0 03-29-2007 03:45 AM
Multiple Search criteria for 1 query browny Queries 2 07-11-2006 11:32 AM




All times are GMT -8. The time now is 05:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World