Date fields with time excluded by Between...And Operator 1

[jhaganjr]

Frustrating problem. I'm using Access 2002, so you know.

Table includes a field with date/times in it. For example ...

6/18/05 4:32:55 PM
6/19/05 5:17:12 PM
etc.

When I query this field with the Between...And operator as criteria, the last date of the operator does NOT capture records with the correct date. Apparently, because of the time in the field.

For example, if my criteria against the above data is "Between #6/18/05 And #6/19/05#, only the first record above is selected. If I manually delete the time portion of second record so it is stored only as 6/19/05, then it is included in the query.

I have tried formatting the data as Format([myfield],"short date"), but this creates crazy results - apparently ignoring the year and pulling many incorrect records.

I've also used DateSerial(Year([myfield]),Month([myfield]),Day([myfield])) - which creates a proper date, but the query delivers a data type mismatch error on the between...and criteria.

Anybody have any insight into this? The basic question is, how do I get the between...and operator to be inclusive of the second date when the values being queried include times?

Thanks a million if you can help here!

 

[PHV]

WHERE Int([DateTime field]) Between ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Use the DateValue() function which is designed to remove the time portion from a date and time value. There is a similar TimeValue() that strips the date part.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jhaganjr]

The DateValue function does return just the date, but the criteria of Between #firstdate# And #lastdate# generates a data type mismatch error.

However, converting the field to an integer - with Int([myfield]) - works! No data type mismatches and the correct data is queried.

I never would have thought of that.

Thank you both for responding!

 

[Du2good]

PHV your tip helped,

I've got a similiar problem, having dates with times. Using

WHERE (((Int([Chargeback_dt])) Between #6/30/2005# And #6/30/2005#))

works (Brings in all 06/30/05 dates) but using the date range supplied from a form wont work yet...How can I get this to work, is it fairly simple?

WHERE (((Int([Chargeback_dt])) Between [Forms]![Arizona Dailys]![StartDt] And [Forms]![Arizona Dailys]![EndDt]))

Thank you in advance!!
Du2Good

 

[PHV]

You may try this as very first line in SQL view:
PARAMETERS [Forms]![Arizona Dailys]![StartDt] DateTime, [Forms]![Arizona Dailys]![EndDt] DateTime;
SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Du2good]

Thanks PHV,

Add another star to your galaxy!! It worked great. I should have called a few hours ago. I've just now briefly looked at Microsoft help regarding parameters, but I don’t understand why having this as very first line in SQL view:


PARAMETERS [Forms]![Arizona Dailys]![StartDt] DateTime, [Forms]![Arizona Dailys]![EndDt] DateTime;

caused the query to work differently? Did it force the query to look at the form when the critea became too complicated? Will you provide some light as to when and why this line should/needs to be added?

Thanks so much
Du2Good

 

[PHV]

This line forces JetSQL to treats your parameters as date values (like the octohorpes in your literal date #6/30/2005#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Du2good]

AH HA,
Sorry, I was too busy to notice "DateTime" twice in the parameter line.
PARAMETERS [Forms]![Arizona Dailys]![StartDt] DateTime, [Forms]![Arizona Dailys]![EndDt] DateTime;
It all makes sense now.

Thankyou
Du2Good