parameter query Retrieve data BETWEEN two dates

[dmp]

I’m using Access 2007 on a vista platform.
I have a parameter query which is run using a form. On the form I have 5 combo boxes: Region, Unit, Installation, Beginning Date and End Date. I want this parameter query to pull all the dates BETWEEN Beginning Date and End Date, however I am having difficulties doing so. I have provided a copy of the query without the BETWEEN statement. Where should the BETWEEN statement be placed?
SELECT INDUCTIONS.REGION, INDUCTIONS.UNIT, INDUCTIONS.INSTALLATION, INDUCTIONS.FINISHED_DATE, INDUCTIONS.END_DATE
FROM INDUCTIONS
WHERE (((INDUCTIONS.REGION)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Or (INDUCTIONS.REGION) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null) AND ((INDUCTIONS.UNIT)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Or (INDUCTIONS.UNIT) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null) AND ((INDUCTIONS.INSTALLATION)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_installation] Or (INDUCTIONS.INSTALLATION) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_installation] Is Null) AND ((INDUCTIONS.FINISHED_DATE)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![LabelB_date] Or (INDUCTIONS.FINISHED_DATE) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![LabelB_date] Is Null) AND ((INDUCTIONS.END_DATE)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Or (INDUCTIONS.END_DATE) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null));

 

[Insane_ai]

Add AND BETWEEN [Field] and [Field] to your WHERE Clause.

 

[dmp]

Thank you for your response. I’m not sure where to place it and how to write it correctly. This is what I have however I receive a syntax error missing operator in query expression. Please see below code.

SELECT INDUCTIONS.REGION, INDUCTIONS.UNIT, INDUCTIONS.INSTALLATION, INDUCTIONS.FINISHED_DATE, INDUCTIONS.END_DATE
FROM INDUCTIONS
WHERE ((INDUCTIONS.REGION)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Or (INDUCTIONS.REGION) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null) AND ((INDUCTIONS.UNIT)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Or (INDUCTIONS.UNIT) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null) AND ((INDUCTIONS.INSTALLATION)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Or (INDUCTIONS.INSTALLATION) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null) AND ((INDUCTIONS.FINISHED_DATE)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Or (INDUCTIONS.FINISHED_DATE) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null) AND ((INDUCTIONS.END_DATE)=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Or (INDUCTIONS.END_DATE) Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null) AND (BETWEEN [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]AND (INDUCTIONS.END_DATE) and [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate)];

 

[DCrake]

You cannot use Like in the way you are doing it

Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null)

 

[dmp]

[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Or Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null This part works for each combo box. Allowing the user to make a selection or have the combo box contain a Null. When I try to pace a BETWEEN statement into the sql that is when I receive a syntax error missing operation in the query

 

[DCrake]

[COLOR="SeaGreen"]
SELECT REGION, UNIT, INSTALLATION, FINISHED_DATE, END_DATE

FROM INDUCTIONS[/COLOR]
[COLOR="Red"]WHERE REGION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] 

Or REGION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null[/COLOR]

[COLOR="Purple"]AND UNIT=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] 

Or UNIT Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null[/COLOR]

[COLOR="DeepSkyBlue"]AND INSTALLATION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] 

Or INSTALLATION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null[/COLOR]
[COLOR="DarkRed"]AND FINISHED_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]

Or FINISHED_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null[/COLOR]
[COLOR="DarkSlateGray"]AND END_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] 

Or END_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null

AND BETWEEN [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] 

[B]AND END_DATE [/B]

and [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate];[/COLOR]

Now that it is a little bit more readable you should be able to spot the mistakes.

I still have never come accross anyone using Like IsNull in the same condition

 

[dmp]

Mr. Crake, I appreciate your time
I’m still receiving the "Syntax error missing operator" message while highlighting "BETWEEN"
SELECT REGION, UNIT, INSTALLATION, FINISHED_DATE, END_DATE
FROM INDUCTIONS
WHERE REGION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region]
Or REGION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null
AND UNIT=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit]
Or UNIT Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null
AND INSTALLATION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]
Or INSTALLATION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null
AND FINISHED_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]
Or FINISHED_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null
AND END_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate]
Or END_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null
AND BETWEEN [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]
AND [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate];

 

[vbaInet]

All the statements where you have this syntax:

Or[COLOR=Navy][B] REGION[/B][/COLOR] [B][COLOR=Red]Like[/COLOR][/B] [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] [COLOR=Red][B]Is Null[/B][/COLOR]

are syntactically wrong.
Explain in words what it means (using the above statement) and we can give you the right syntax.

 

[dmp]

Thank you for your time. I hope I have answered your question:
cmb_INDUCTIONS_region is the Combo box name in the form
REGION is a column name on the table
The end-user does not have to select a “REGION” for the parameter query to run. For this reason I wrote: Or REGION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null
When I run the below query, without the BETWEEN statement, it runs without errors. My problem appears when I build on this query. I would like the end-user to select two dates and the query to produce the “UNITs” that are affected during those two dates.
SELECT REGION, UNIT, INSTALLATION, FINISHED_DATE, END_DATE
FROM INDUCTIONS
WHERE REGION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region]
Or REGION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null
AND UNIT=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit]
Or UNIT Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_unit] Is Null
AND INSTALLATION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]
Or INSTALLATION Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null
AND FINISHED_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate]
Or FINISHED_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_bdate] Is Null
AND END_DATE=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate]
Or END_DATE Like [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_edate] Is Null;

 

[vbaInet]

Yes it will work but it will not produce the right results when a value is entered. Look at it again!

The only way it will work is like this:

Or REGION Like IIF(IsNull([Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region]), *, [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region])

OR

Or REGION Like Nz([Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region],  *)

But I would advise you to build your sql in code instead.

 

[Brianwarnock]

You do not need the Region Like Just
REGION=[Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region]
Or [Forms]![frm_COMPLETE_INDUCTIONS_User_Request]![cmb_INDUCTIONS_region] Is Null


brian

 

[vbaInet]

Brian, here's the motivation to the proposed two solutions:

The end-user does not have to select a “REGION” for the parameter query to run.

With what you wrote, if a selection is made in the Region control, it will be parsed as:

<Value> OR Is Null

Which is fine!

When nothing is selected, you get:

Null OR Is Null

Which will yield 0 results.

 

[Brianwarnock]

Nope,

One check is for the value of the field to equal that of the cmbo if that is met then those records are selected, if not then nothing is selected, unless the cmbo is null then all are selected.
Read the link

Brian

 

[vbaInet]

Oh yes, I didn't notice Region wasn't specified in the OR part. Quite clever that!

 

[Brianwarnock]

But as you pointed out to me in a previous thread when I suggested this as a solution rather than adding All to the combo list, you cannot search for Nulls

Brian

 

[vbaInet]

But as you pointed out to me in a previous thread when I suggested this as a solution rather than adding All to the combo list, you cannot search for Nulls

Brian

Absolutely right!

 

[Brianwarnock]

Absolutely right!

I usually am.

Brian

 

[vbaInet]

I usually am.

Brian

Maybe Bob or David can validate that