Parameter between two dates

[stu_c]

Hi all
I have got a query i need to be able to search between two dates in two different
Date From
Date Till

is there a code in the querty i can searhc for is with the dates in two separate fields?

Eg.
Date from: 01/01/2011
Date Till: 11/02/2011

and it finds all the records between these dates?

 

[DCrake]

use

>= [Start Date]


and


<=[End Date]

in the condition row in the respective columns

 

[stu_c]

slight problem just tried it and for some reason if i have the following records

RECORD 1
Date from: 11/03/2011
Date Till: 13/03/2011

RECORD 2
Date from: 12/03/2011
Date Till: 14/03/2011

and i do a query search of 11/03/2011 - 13/03/2011 it will only find RECORD 1 but in fact record 2 should also show as its in use between these dates?

 

[DCrake]

Put them on seperate lines in the condition section

 

[stu_c]

seem to be having trouble worked fine at home but at work i have got 2002 and when i do the following code.
>= [Start Date]
<=[End Date]

if they are both on the same line it will only find the booking that starts on the and ends on the end specified.

if i put on separate lines for some reason it shows all bookings disregarding any dates specified

 

[stu_c]

heres what i have done and still no luck even both on the same line

is it possible to do it through SQL?
current code
SELECT [QRY - FLEET BOOKINGS].Forename, [QRY - FLEET BOOKINGS].Surname, [QRY - FLEET BOOKINGS].Office, [QRY - FLEET BOOKINGS].[Date From], [QRY - FLEET BOOKINGS].[Time From], [QRY - FLEET BOOKINGS].Destination, [QRY - FLEET BOOKINGS].[Date Till], [QRY - FLEET BOOKINGS].[Time Till], [QRY - FLEET BOOKINGS].[Car Registration], [QRY - FLEET BOOKINGS].NOTES
FROM [QRY - FLEET BOOKINGS];

 

[vbaInet]

For dates I prefer,

BETWEEN [Start Date] AND [End Date]

 

[stu_c]

For dates I prefer,

BETWEEN [Start Date] AND [End Date]

if i am totally honest that really hasnt helped

 

[vbaInet]

It hasn't helped in what way? The results are the same or you don't understand what was written?

 

[stu_c]

it has to be in a parametre query so the user can specify the start and end date and the results bring back all bookings between thoes dates.

the start and end date are in two separate fields i have tried
>= [Start Date] in start date column
<=[End Date] in end end date column as show in the picture but no luck working.

the code u have given me
BETWEEN [Start Date] AND [End Date] wont work as it needs to look up in two fields

 

[vbaInet]

If you want the criteria to affect both dates simultaneously between the dates you entered, then the BETWEEN criteria needs to be in the Criteria row of both fields.

 

[vbaInet]

Got your PM.

SELECT Forename, Surname, Office, [Date From], [Time From], Destination, [Date Till], [Time Till], [Car Registration], NOTES
FROM [QRY - FLEET BOOKINGS]
WHERE ([Date From] BETWEEN [Please enter Start Date] AND [Please enter End Date]) AND ([Date Till] BETWEEN [Please enter Start Date] AND [Please enter End Date]);

Copy and paste and run it.

 

[stu_c]

still having the same problem!

for example if i have the following bookings

NAME - DATE FROM - DATE TILL

Williams - 24/03/2011 - 30/032011
Kent - 22/03/2011 - 25/032011
Smith - 22/03/2011 - 23/03/2011

If i have a query to look up hires between 24/032011 to 01/04/2011

Currently it is only picking up Williams booking and should infact pick up Both Williams & Kent as kents booking is also between these dates?

 

[vbaInet]

Change AND to OR.

 

[stu_c]

Seemed to work great until i put in a date 27/03/2011 till 28/03/2011
and it didnt pick up any records at all where in fact it should have picked up Williams Booking

the SQL i am using is:

WHERE ((([QRY - FLEET BOOKINGS].[Date From]) Between [Please enter Start Date] And [Please enter End Date])) OR ((([QRY - FLEET BOOKINGS].[Date Till]) Between [Please enter Start Date] And [Please enter End Date]));

 

[vbaInet]

How about:

SELECT Forename, Surname, Office, [Date From], [Time From], Destination, [Date Till], [Time Till], [Car Registration], NOTES
FROM [QRY - FLEET BOOKINGS]
WHERE (CLng(Nz([Date From], -1)) BETWEEN CLng(Nz([Please enter Start Date])) AND CLng(Nz([Please enter End Date])) OR (CLng(Nz([Date Till], -1)) BETWEEN CLng(Nz([Please enter Start Date])) AND CLng(Nz([Please enter End Date])));

 

[nightflight]

I did something similar i think a while back, hopefully you'll be able to pick this apart and pull out the bits you need, in the date colulmn of the query design view, criteria one was

Between IIf(IsNull([Forms]![frmquerydept]![txtStart]),0,[Forms]![frmquerydept]![txtStart]) And IIf(IsNull([Forms]![frmquerydept]![txtEnd]),99999,[Forms]![frmquerydept]![txtEnd])

in the "or" criteria line below that was

Between IIf(IsNull([Forms]![frmquerydept]![txtStart]),0,[Forms]![frmquerydept]![txtStart]) And IIf(IsNull([Forms]![frmquerydept]![txtEnd]),99999,[Forms]![frmquerydept]![txtEnd])

This lets you specify return;
a) records between start date and end date OR
b) records after specified start date OR
c) records before specified end date

 

[JamesMcS]

Think this one is solved in the other thread you created about it.....