Query based on multiple criteria from form w/ between dates

[iamsophie]

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:

SELECT Coverage.Week, Coverage.Area
   , Coverage.Name, Coverage.[Store Type]
   , Coverage.[Dem Detail], Coverage.Date
   , Coverage.[Dem Type 2], Coverage.[Coverage Status]
   , Coverage.[Date Covered], Coverage.[Dem Type 1]
FROM Coverage, Coverage 
   AS Coverage_1
WHERE (((Coverage.Area) 
   Like NZ([Forms]![Coverage Form]![Area],"") 
   OR NZ([Forms]![Coverage Form]![Area],"")="")
      >False) 
   AND (((Coverage.Name) 
      Like NZ([Forms]![Coverage Form]![StoreName],"")
   OR NZ([Forms]![Coverage Form]![StoreName],"")="")
      <>False) 
   AND (((Coverage.[Store Type]) 
      Like NZ([Forms]![Coverage Form]![StoreType],"")
   OR NZ([Forms]![Coverage Form]![StoreType],"")="")
      <>False) 
   AND (((Coverage.[Dem Detail]) 
      Like "*" & NZ([Forms]![Coverage Form]![DemName],"")
       & "*" 
   OR NZ([Forms]![Coverage Form]![DemName],"")="")
      <>False) 
   AND (((Coverage.Date) 
      Like NZ([Forms]![Coverage Form]![ExactDate],"")
   OR NZ([Forms]![Coverage Form]![ExactDate],"")="")
      <>False) 
   AND (((Coverage.[Dem Type 2]) 
      Like NZ([Forms]![Coverage Form]![DemType],"") 
   OR NZ([Forms]![Coverage Form]![DemType],"")="")
      <>False) 
   AND (((Coverage.[Coverage Status]) 
      Like "*" & NZ([Forms]![Coverage Form]![CoverageStatus],"") 
      & "*" 
   OR NZ([Forms]![Coverage Form]![CoverageStatus],"")="")
      <>False) 
   AND (((Coverage.[Dem Type 1]) 
      Like NZ([Forms]![Coverage Form]![DemGroup],"")
   OR NZ([Forms]![Coverage Form]![DemGroup],"")="")
      <>False);

So I'd need something like this:

AND ((((((Coverage.Date) 
   Between NZ([Forms]![Coverage Form]![FromDate]," ")
      AND NZ([Forms]![Coverage Form]![ToDate]," ") 
   OR NZ([Forms]![Coverage Form]![FromDate]," ")="")
      <>False) 
   OR NZ([Forms]![Coverage Form]![ToDate]," ")="")
      <>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

[jforbes]

Hello again Sophie,

This will probably work:

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

AND ((Coverage.Date) >= NZ([Forms]![Coverage Form]![FromDate],"") OR NZ([Forms]![Coverage Form]![FromDate],"")="")
AND ((Coverage.Date) <= NZ([Forms]![Coverage Form]![ToDate],"") OR NZ([Forms]![Coverage Form]![ToDate],"")

Again, I just typed this in, hopefully there aren't too many syntax errors.

[iamsophie]

Thanks for your help again!

[NeoPa]

Hi Joe.

An alternative (and simplified) way of handling those expressions could be :

AND ([Coverage].[Date]>=NZ([Forms]![Coverage Form]![FromDate],#1/1/1900#)
AND ([Coverage].[Date]<=NZ([Forms]![Coverage Form]![FromDate],#12/31/9999#)

[jforbes]

Good call, NeoPa. Much cleaner.

[NeoPa]

Of course (Says he after missing the blindingly obvious last time round), this means the Between approach can now be used quite reliably too ;-)

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.