Using between function with General Date

[Ted]

Hi all,

I'm trying to select records from a table using the Between function. For
example

Between #7/1/09# and #7/31/09#

The problem I'm having is the Date format of the field is General Date. I
have a record dated 7/31/09 11:18:13 AM and it's not including it.

Is there a way around this besides changing my Between statement to Between
#7/1/09# and #8/1/09#?

TIA...this is driving me nuts.
Ted

 

[John W. Vinson]

Hi all,

I'm trying to select records from a table using the Between function. For
example

Between #7/1/09# and #7/31/09#

The problem I'm having is the Date format of the field is General Date. I
have a record dated 7/31/09 11:18:13 AM and it's not including it.

Is there a way around this besides changing my Between statement to Between
#7/1/09# and #8/1/09#?

TIA...this is driving me nuts.
Ted

This isn't really the format causing the problem - the format just controls
HOW the value is displayed, not what's stored. In point of fact, the instant
of time #7/31/09 11:18:13 AM# is NOT between 7/1 and 7/31 - it's about eleven
hours too late!

I'd suggest either

= #7/1/2009# AND < #8/1/2009#

or, better, use a parameter query soliciting your date range from a Form:

= CDate([Forms]![YourFormName]![txtStartDate]) AND < DateAdd("d", 1, CDate([Forms]![YourFormName]![txtEndDate]))

 

[Jeff Boyce]

Ted

It sounds like the field holds date/time values, not date values.

If you need to preserve the date/time values, then you'll want to check for
records where the DateValue() of the field is between your two dates...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ted]

Thanks Jeff, that does make sense. I'll try that.

 

[Ted]

Thanks John. We just switched over to a new system so I guess they are
storing their date fields differently. I'll have to get the Date from the
field or use 8/1/09 instead of 7/31/09. Thanks for your help.

Hi all,

I'm trying to select records from a table using the Between function. For
example

Between #7/1/09# and #7/31/09#

The problem I'm having is the Date format of the field is General Date. I
have a record dated 7/31/09 11:18:13 AM and it's not including it.

Is there a way around this besides changing my Between statement to
Between
#7/1/09# and #8/1/09#?

TIA...this is driving me nuts.
Ted

This isn't really the format causing the problem - the format just
controls
HOW the value is displayed, not what's stored. In point of fact, the
instant
of time #7/31/09 11:18:13 AM# is NOT between 7/1 and 7/31 - it's about
eleven
hours too late!

I'd suggest either

= #7/1/2009# AND < #8/1/2009#

or, better, use a parameter query soliciting your date range from a Form:

= CDate([Forms]![YourFormName]![txtStartDate]) AND < DateAdd("d", 1,
CDate([Forms]![YourFormName]![txtEndDate]))