Long Date / Between and...

[Peter]

Hi all,
I just converted from 2003 to 2007 and at the same time I took the
opportunity to “clean up†some in tables…I changed [DateCreated] format from
short to long date since the Date/Time stamp
Is valuable (previous I used one separate date and time) ..Here is the
issue. Running the query with the parameters in the [DateCreated] field,
Between(First Date) AND (Second Date) does not function.. what am I doing
wrong?

Thanks!

 

[Douglas J. Steele]

Changing the format of a field doesn't actually change what's stored in the
field: it just changes how it's presented.

If DateCreated contains both date and time and FirstDate and SecondDate are
both the same day, you won't retrieve any rows. Instead, try:

[DateCreated] >= First Date AND [DateCreated] < Second Date

 

[Dirk Goldgar]

Changing the format of a field doesn't actually change what's stored in
the field: it just changes how it's presented.

If DateCreated contains both date and time and FirstDate and SecondDate
are both the same day, you won't retrieve any rows. Instead, try:

[DateCreated] >= First Date [DateCreated] < Second Date

Doug, shouldn't that second criterion be,

[DateCreated] < (SecondDate + 1)

or the equivalent using DateAdd to add a day?

Assuming that FirstDate and SecondDate are entered as dates only, not
dates+times, that is.

 

[Douglas J. Steele]

Good catch, Dirk.

I was focussing on the < as opposed to <= and forgot to change the value!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Changing the format of a field doesn't actually change what's stored in
the field: it just changes how it's presented.

If DateCreated contains both date and time and FirstDate and SecondDate
are both the same day, you won't retrieve any rows. Instead, try:

[DateCreated] >= First Date [DateCreated] < Second Date

Doug, shouldn't that second criterion be,

[DateCreated] < (SecondDate + 1)

or the equivalent using DateAdd to add a day?

Assuming that FirstDate and SecondDate are entered as dates only, not
dates+times, that is.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Peter]

Thank you very much Douglas and Dirk..!

Changing the format of a field doesn't actually change what's stored in the
field: it just changes how it's presented.

If DateCreated contains both date and time and FirstDate and SecondDate are
both the same day, you won't retrieve any rows. Instead, try:

[DateCreated] >= First Date AND [DateCreated] < Second Date

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
I just converted from 2003 to 2007 and at the same time I took the
opportunity to "clean up" some in tables.I changed [DateCreated] format
from
short to long date since the Date/Time stamp
Is valuable (previous I used one separate date and time) ..Here is the
issue. Running the query with the parameters in the [DateCreated] field,
Between(First Date) AND (Second Date) does not function.. what am I doing
wrong?

Thanks!

.