criteria date range

[Guest]

Hello, I need some help to generate a query that pulls information on from a
range of dates.

I have a table called "client services" fields include: client name, client
number, date of service, total time, type of service, provider name, location
of service.

I would like to create a report to see all services a provider has rendered
with a given time set. The query fields include: date of service, total time,
and provider name. I need some help with the date criteria. For instance I
would like the query to pull dates of service from 5/20/07 to 5/31/07.... how
would this look in the critieria field? Also is their a way to have access
ask me range of dates of service to pull from each time I double click on the
query...or whatever I have to do?

Thanks for you help,
-Mark

 

[Guest]

Use this as criteria ---
Between [Enter start date] And [Enter end date]

Bear in mind the dates are inclusive except if your dates have time included
then the time is a decimal fraction larger than the actual date. The date
with the fraction will not be included.

 

[Jamie Collins]

Use this as criteria ---
Between [Enter start date] And [Enter end date]

Bear in mind the dates are inclusive except if your dates have time included

Access/Jet has but one temporal data type, named DATETIME to remind
you that such a value *always* comprises a time element. Likewise,
BETWEEN is *always* inclusive in Access/Jet.

One approach is to 'round' the 'start date' to the first time granule
of the day and the 'end date' to the last time granule of the day
(noting the smallest supported DATETIME granule is one second) e.g.

BETWEEN DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter start
date]), #1990-01-01 00:00:00#) AND
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter end date]),
#1990-01-01 23:59:59#)

Jamie.

--

 

[Guest]

Thank you Karl and Jamie. I ended up using "Between [Enter start date] And
[Enter end date]" very cool.

Use this as criteria ---
Between [Enter start date] And [Enter end date]

Bear in mind the dates are inclusive except if your dates have time included

Access/Jet has but one temporal data type, named DATETIME to remind
you that such a value *always* comprises a time element. Likewise,
BETWEEN is *always* inclusive in Access/Jet.

One approach is to 'round' the 'start date' to the first time granule
of the day and the 'end date' to the last time granule of the day
(noting the smallest supported DATETIME granule is one second) e.g.

BETWEEN DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter start
date]), #1990-01-01 00:00:00#) AND
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Enter end date]),
#1990-01-01 23:59:59#)

Jamie.