473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Query based on multiple criteria from form w/ between dates

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.

5 1583
jforbes
1,107 Expert 1GB
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
Thanks for your help again!
Jun 5 '15 #3
NeoPa
32,556 Expert Mod 16PB
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
1,107 Expert 1GB
Good call, NeoPa. Much cleaner.
Jun 9 '15 #5
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: JDJones | last post by:
Using PHP and MySQL. Trying to put a list of categories into a drop down select option of a form like: <form name="form" action="<? print $_SERVER?>" method="get"> <select name="subject">...
2
by: William Smith | last post by:
Access 97 - need to extract from large database a dynaset of "contacts" based on zip codes. If I have 1000+ names and need to extract 100+ zip codes from them, what is easiest way to query this? I...
6
by: sheree | last post by:
I would like to create a query where one of the columns of the queries comes from a combo list box on a form. For example, if my table has the following fields: id name interest1 interest2...
16
by: tiarynn | last post by:
Hi everyone, I am a beginning to intermediate user with Access 2000 on XP. I am trying to create a query from a table of more than 1300 records. I need to look up individuals by their SSN only...
11
by: RZ15 | last post by:
Hi, I have a monthly sales query that sums monthly sales by product (each month has its own field from January last year until December of the current year). A user of this table will be prompted...
49
by: martin DH | last post by:
Hello all, I'm back with another SQL related problem. The details are below, but in short: I am using Access 2003. I have a table whose structure may include four different associate names per...
1
by: Karen D | last post by:
Help Again!! I’m using Access 2003 and I have a form that allows users to enter criteria for selecting tables and queries as well as the query parameters that will be used to generate a report. The...
1
by: BarbQb | last post by:
Hi All, I am working on a ranking Items in a query and I am stuck when I need to take more fields into account for the rank. Currently I have a query that ranks by the they were entered...
4
by: echamorro | last post by:
Hi there, I am new in VBA. I created a query from a table that lists several fields. Every time the query is used, it asks the user to enter the part number he/she wants to see and the quantity to...
2
by: echamorro | last post by:
Hi all. I need to add one more criteria to my query. Two will be taken from a combo box and third one will be from one of the fields from the query in form of a text. It is not working. Could someone...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.