querying time stamped date field

[DeanRowe]

Hi,

I use a query to return values between two dates, here's the code I use:

Between [Select Start Date:] And [Select End Date:]

However because my dates are time stamped (they need to be!) the query omits anything on the end date, for example:

Between [01/09/2007] And [05/09/2007] will return values for the 1st, 2nd, 3rd, 4th but not the 5th - because (I think I'm right in thinking this but I might not be!) it only returns values upto midnight on the 4th? so 05/09/2007 13.42pm won't show up because it's after 11.59 on the 4th.

I can't ask people to enter in an extra day because quite alot of people who use our database won't remember and it'll cause alot of problems when they forget and get the wrong figures.

I've tried adding the following:
" & "11:59:59""

onto the code but Access says the expression is typed wrong or is too complicated to be evaluated.

Does anyone know how to fix this code please??

thank you for your time

[Moniker]

Between CDate([Select Start Date:]) And CDate([Select End Date:])

[DeanRowe]

Hi Moniker,

Thanks for the reply - the code performed the query as normal but didn't solve the problem, tried moving the brackets and using "speech marks" but no luck. Cheers for trying tho Moniker

[Dennisk]

format the dates to remove the time component

[Steve R.]

Two possible approaches.
1. Don't use BETWEEN instead ">=date1 AND <= Date2"
2. If you use BETWEEN use the date before and the date after. BETWEEN means between the selected time period, but NOT the limits. BETWEEN is equivalent to " > date1 AND < date2".

[neileg]

Originally Posted by DeanRowe

I've tried adding the following:
& "11:59:59""

onto the code but Access says the expression is typed wrong or is too complicated to be evaluated.

Right idea, wrong implementation. Using the ampersand and a text string turns the date entered into a string and this can't then be compared with a date. So you could:
1) Use the formula you tried but convert to a date using CDate()
2) Use DateAdd() to do proper date arithmetic to add 11:59:59
3) Use DateAdd() to add a whole day but use > < instead of between as suggested above

There are other subtle variations but that should get you started.

[Brianwarnock]

Dean you are correct in that if times are not quoted they default to 00:00:00

but surely you need to add 23:59:59 to capture everything on the last day!?
If you know that nothing ever happens at midnight, then you can use dateadd to add 1 day and use Between ..And.. but it would be more correct to use >=... And <...

Brian

[EMP]

One more option. You can use the DateValue() function to extract the dates. In query SQL View, you can change the Where clause to:-

WHERE DateValue([DateTimeField]) Between [Select Start Date] And [Select End Date]


But the following should be more efficient than extracting the dates from the field:-

WHERE [DateTimeField] Between [Select Start Date] And CDATE([Select End Date])+#23:59:59#

Date/time data type is a number, so we use the + sign.

^

[Bilbo_Baggins_Esq]

Perhaps I'm being a little dense here, but why couldn't it just be as simple as:

Between [Select Start Date:] And ([Select End Date:]+1)


?

[Brianwarnock]

Bilbo you need to use CDate as per EMP, plus you would select Enddate+1 00:00:00.

I believe what you are suggesting did work prior to ACCESS 2002.

Is using CDate more efficient than Dateadd,
and Between... And ....better than>= ... And < ... ?


Brian

[Bilbo_Baggins_Esq]

Hmmmm, that's odd because I already tested it and it works terrific just as it is...

Since the date is actually numeric, adding one to the entered end date simply ensures that all date/time stamps up to midnight of that day are included.

[Brianwarnock]

Sorry Bilbo I just edited my post, you see I was going to suggest what EMP has done without the CDate, but being retired and nobody else suggesting it I didn't trust my memory and ran a test it didn't work, but after you suggestion dug out an old 2000 db and thats what I had [enddate]+#23:59:59#, but although it must have worked then it does not work on my 2002 system.

Brian

[Brianwarnock]

I still say that if you add 1 day and use Between....And... you are not strictly correct
Say the sates are , using English format. 01/01/07 and 31/01/07, by adding a day you will Select from 01/01/07 00:00:00 to 01/02/07 00:00:00, just as those who suggested using> and< would miss 01/01/07 00:00:00 it must be >= And <

Brian