Date/Time field - midnight

[Mary]

I am querying records between two dates. The results are
only pulling records where the time is midnight for the
end date entered. I know there's a way around this but
can't remember what it is. Can you help?

Between [Enter the first Business Day of the Month] And
[Enter the previous Saturday date]

Thanks!
Mary

 

[MikeC]

Mary,

Here's one way to solve the problem:

Between DateValue([Enter the first Business Day of the Month]) And
DateValue([Enter the previous Saturday date])

 

[John Vinson]

I am querying records between two dates. The results are
only pulling records where the time is midnight for the
end date entered. I know there's a way around this but
can't remember what it is. Can you help?

Between [Enter the first Business Day of the Month] And
[Enter the previous Saturday date]

Thanks!
Mary

Short answer:

= DateValue([Enter the first Business Day of the Month] And

< DateAdd("d", 1, [Enter the previous Saturday date])

Sneaky answer:

= DateSerial(Year(Date()), [Enter month number:], 1) AND < DateAdd("d", 1-Weekday(Date(), vbSaturday), Date())

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Mary]

Thanks, that worked! I have another twist, same basic
principal.

Between [Enter Start Date] And [Enter End Date]

This also misses records that start at any time other than
00:00 for the end date. I can manually type in the Date
followed by 23:59 to get those records, but would prefer
not to.

Thanks again,
Mary

-----Original Message-----

I am querying records between two dates. The results are
only pulling records where the time is midnight for the
end date entered. I know there's a way around this but
can't remember what it is. Can you help?

Between [Enter the first Business Day of the Month] And
[Enter the previous Saturday date]

Thanks!
Mary

Short answer:

= DateValue([Enter the first Business Day of the Month]

And
< DateAdd("d", 1, [Enter the previous Saturday date])

Sneaky answer:

= DateSerial(Year(Date()), [Enter month number:], 1) AND

< DateAdd("d", 1-Weekday(Date(), vbSaturday), Date())

 

[John Vinson]

Thanks, that worked! I have another twist, same basic
principal.

Between [Enter Start Date] And [Enter End Date]

This also misses records that start at any time other than
00:00 for the end date. I can manually type in the Date
followed by 23:59 to get those records, but would prefer
not to.

Please reread my suggestion. The DateAdd() function in it takes care
of this.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John. I just stumbled along this suggestion and it worked well for
what my database. I find your answers and this public online access chat
excellent.

I am querying records between two dates. The results are
only pulling records where the time is midnight for the
end date entered. I know there's a way around this but
can't remember what it is. Can you help?

Between [Enter the first Business Day of the Month] And
[Enter the previous Saturday date]

Thanks!
Mary

Short answer:

= DateValue([Enter the first Business Day of the Month] And

< DateAdd("d", 1, [Enter the previous Saturday date])

Sneaky answer:

= DateSerial(Year(Date()), [Enter month number:], 1) AND < DateAdd("d", 1-Weekday(Date(), vbSaturday), Date())

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps