By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Query based on multiple criteria from form w/ between dates

P: 6
Hi All,

I have a form with multiple fields (e.g. exact date, store name, store type, coverage status etc.). The user can fill in any of them and then a query returns the results.

Everything works perfectly, but when I try to put the between dates in as well in SQL view, it keeps returning an error message (Expression is typed in incorrectly.)

I'm fairly new to Access and can't figure out how to include the From and To date in the below code:

Expand|Select|Wrap|Line Numbers
  1. SELECT Coverage.Week, Coverage.Area
  2.    , Coverage.Name, Coverage.[Store Type]
  3.    , Coverage.[Dem Detail], Coverage.Date
  4.    , Coverage.[Dem Type 2], Coverage.[Coverage Status]
  5.    , Coverage.[Date Covered], Coverage.[Dem Type 1]
  6. FROM Coverage, Coverage 
  7.    AS Coverage_1
  8. WHERE (((Coverage.Area) 
  9.    Like NZ([Forms]![Coverage Form]![Area],"") 
  10.    OR NZ([Forms]![Coverage Form]![Area],"")="")
  11.       >False) 
  12.    AND (((Coverage.Name) 
  13.       Like NZ([Forms]![Coverage Form]![StoreName],"")
  14.    OR NZ([Forms]![Coverage Form]![StoreName],"")="")
  15.       <>False) 
  16.    AND (((Coverage.[Store Type]) 
  17.       Like NZ([Forms]![Coverage Form]![StoreType],"")
  18.    OR NZ([Forms]![Coverage Form]![StoreType],"")="")
  19.       <>False) 
  20.    AND (((Coverage.[Dem Detail]) 
  21.       Like "*" & NZ([Forms]![Coverage Form]![DemName],"")
  22.        & "*" 
  23.    OR NZ([Forms]![Coverage Form]![DemName],"")="")
  24.       <>False) 
  25.    AND (((Coverage.Date) 
  26.       Like NZ([Forms]![Coverage Form]![ExactDate],"")
  27.    OR NZ([Forms]![Coverage Form]![ExactDate],"")="")
  28.       <>False) 
  29.    AND (((Coverage.[Dem Type 2]) 
  30.       Like NZ([Forms]![Coverage Form]![DemType],"") 
  31.    OR NZ([Forms]![Coverage Form]![DemType],"")="")
  32.       <>False) 
  33.    AND (((Coverage.[Coverage Status]) 
  34.       Like "*" & NZ([Forms]![Coverage Form]![CoverageStatus],"") 
  35.       & "*" 
  36.    OR NZ([Forms]![Coverage Form]![CoverageStatus],"")="")
  37.       <>False) 
  38.    AND (((Coverage.[Dem Type 1]) 
  39.       Like NZ([Forms]![Coverage Form]![DemGroup],"")
  40.    OR NZ([Forms]![Coverage Form]![DemGroup],"")="")
  41.       <>False);
So I'd need something like this:
Expand|Select|Wrap|Line Numbers
  1. AND ((((((Coverage.Date) 
  2.    Between NZ([Forms]![Coverage Form]![FromDate]," ")
  3.       AND NZ([Forms]![Coverage Form]![ToDate]," ") 
  4.    OR NZ([Forms]![Coverage Form]![FromDate]," ")="")
  5.       <>False) 
  6.    OR NZ([Forms]![Coverage Form]![ToDate]," ")="")
  7.       <>False);
I can kind of see this is not going to work, but I have no idea how to fix it. Could you please help?

Thanks,
Sophie
Jun 5 '15 #1

✓ answered by jforbes

Hello again Sophie,

This will probably work:
Expand|Select|Wrap|Line Numbers
  1. AND ((Coverage.Date BETWEEN [Forms]![Coverage Form]![FromDate] AND [Forms]![Coverage Form]![ToDate] OR NZ([Forms]![Coverage Form]![FromDate],"")="" OR NZ([Forms]![Coverage Form]![ToDate],"")=""))
If you truly want a Between in your Where Clause there really needs to be both the Start and End supplied before any results are returned. I tend to avoid this when possible. Instead I use two separate expressions, a Greater than expression and a Less than expression. This also gives the flexibility of allowing the user to supply a Start date only and get all records after the Start date. Or to cut off the returned records up to a date by supplying the End Date
Expand|Select|Wrap|Line Numbers
  1. AND ((Coverage.Date) >= NZ([Forms]![Coverage Form]![FromDate],"") OR NZ([Forms]![Coverage Form]![FromDate],"")="")
  2. AND ((Coverage.Date) <= NZ([Forms]![Coverage Form]![ToDate],"") OR NZ([Forms]![Coverage Form]![ToDate],"")
  3.  
Again, I just typed this in, hopefully there aren't too many syntax errors.

Share this Question
Share on Google+
5 Replies


jforbes
Expert 100+
P: 1,107
Hello again Sophie,

This will probably work:
Expand|Select|Wrap|Line Numbers
  1. AND ((Coverage.Date BETWEEN [Forms]![Coverage Form]![FromDate] AND [Forms]![Coverage Form]![ToDate] OR NZ([Forms]![Coverage Form]![FromDate],"")="" OR NZ([Forms]![Coverage Form]![ToDate],"")=""))
If you truly want a Between in your Where Clause there really needs to be both the Start and End supplied before any results are returned. I tend to avoid this when possible. Instead I use two separate expressions, a Greater than expression and a Less than expression. This also gives the flexibility of allowing the user to supply a Start date only and get all records after the Start date. Or to cut off the returned records up to a date by supplying the End Date
Expand|Select|Wrap|Line Numbers
  1. AND ((Coverage.Date) >= NZ([Forms]![Coverage Form]![FromDate],"") OR NZ([Forms]![Coverage Form]![FromDate],"")="")
  2. AND ((Coverage.Date) <= NZ([Forms]![Coverage Form]![ToDate],"") OR NZ([Forms]![Coverage Form]![ToDate],"")
  3.  
Again, I just typed this in, hopefully there aren't too many syntax errors.
Jun 5 '15 #2

P: 6
Thanks for your help again!
Jun 5 '15 #3

NeoPa
Expert Mod 15k+
P: 31,768
Hi Joe.

An alternative (and simplified) way of handling those expressions could be :
Expand|Select|Wrap|Line Numbers
  1. AND ([Coverage].[Date]>=NZ([Forms]![Coverage Form]![FromDate],#1/1/1900#)
  2. AND ([Coverage].[Date]<=NZ([Forms]![Coverage Form]![FromDate],#12/31/9999#)
Jun 9 '15 #4

jforbes
Expert 100+
P: 1,107
Good call, NeoPa. Much cleaner.
Jun 9 '15 #5

NeoPa
Expert Mod 15k+
P: 31,768
Of course (Says he after missing the blindingly obvious last time round), this means the Between approach can now be used quite reliably too ;-)
Expand|Select|Wrap|Line Numbers
  1. AND ([Coverage].[Date] Between Nz([Forms]![Coverage Form]![FromDate],#1/1/1900#) And Nz([Forms]![Coverage Form]![FromDate],#12/31/9999#))
Personally, I really don't advise linking forms to queries in this way if it can be avoided, but I know that the dynamic nature of it appeals to many - especially those starting out. I find that it restricts the ability to use your queries more flexibly as projects progress and you end up duplicating logic, which leads in turn to projects that are harder to maintain. Nevertheless, this is a way to use that approach and still minimise the code that's required.
Jun 9 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.