Date Range

[SoloUser]

How do you set a parameter value prompt in a query to look for a date range
rather then just a singular date

 

[Ron2006]

make the criteria for the query


Between [Enter the start date] and [Enter the end date]


Ron

 

[ANDY-N via AccessMonster.com]

Query Criteria: Between [Enter Beginning Date (mm/dd/yyyy)] And [Enter Ending
Date(mm/dd/yyyy)]

The format is however the date column is set at, mm/dd/yyyy etc....

 

[Jerry Whittle]

Hi Ron2006,

One small problem is if there is a time attached to the date. I do something
like this to get around it.

Between [Enter the start date] and [Enter the end date] + 0.99999

The + 0.99999 puts the time up to 11:59:59 PM and therefore covers the
entire day.

 

[Ron2006]

You are right Jerry. I forgot about that part.

I have also seen it done as

Between [Enter the start date] and [Enter the end date] + 1

(of course this assumes you will never have anything entered at time
00:00:00 of the next day.)

Ron

 

[Dale Fye]

Or you could use the DateValue function to force the date into a date only
format.

--
Dale

email address is invalid
Please reply to newsgroup only.

Hi Ron2006,

One small problem is if there is a time attached to the date. I do something
like this to get around it.

Between [Enter the start date] and [Enter the end date] + 0.99999

The + 0.99999 puts the time up to 11:59:59 PM and therefore covers the
entire day.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

make the criteria for the query


Between [Enter the start date] and [Enter the end date]


Ron

 

[Rick Brandt]

Or you could use the DateValue function to force the date into a date
only format.

No, no, no.

Never apply criteria to an expression if there is another way to do it.

 

[Dale Fye]

So you're saying to avoid criteria like:

WHERE DateValue([DateField]) BETWEEN #1/1/07# and #12/31/07#

Why? I don't think I've ever heard that mentioned before.

Dale

 

[Rick Brandt]

So you're saying to avoid criteria like:

WHERE DateValue([DateField]) BETWEEN #1/1/07# and #12/31/07#

Why? I don't think I've ever heard that mentioned before.

The big reason is the loss of index use. If your date field has an index
then only a criteria against the field name alone will be able to utilize
that index. For example, with a numeric field even a do-nothing expression
like...

FieldName + 0

....would prevent the query engine from using the index on FieldName and
forces a full table scan.

Admittedly in most small Jet databases (particularly with local tables) the
difference would not be noticeable, but it is a bad habit to get into
because many small local tables evolve into large networked tables over time
(or get moved to server back ends) and this sort of querying will really
slow things down in those environments.

A big problem is that the in-efficient query criteria is usually the easiest
to understand and takes fewer keystrokes to write so they are used a LOT and
recommended to others a LOT. It has become one of my pet topics of late to
point out the disadvantages.

 

[Jerry Whittle]

Hi Rick,

Slighty off topic, but some databases, newer versions of Oracle for example,
have function-based indexes to get around this problem. The index is built
with the function taken into consideration. They aren't as efficient as a
regular index, but beat doing a full table scan where there are millions of
records.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

So you're saying to avoid criteria like:

WHERE DateValue([DateField]) BETWEEN #1/1/07# and #12/31/07#

Why? I don't think I've ever heard that mentioned before.

The big reason is the loss of index use. If your date field has an index
then only a criteria against the field name alone will be able to utilize
that index. For example, with a numeric field even a do-nothing expression
like...

FieldName + 0

....would prevent the query engine from using the index on FieldName and
forces a full table scan.

Admittedly in most small Jet databases (particularly with local tables) the
difference would not be noticeable, but it is a bad habit to get into
because many small local tables evolve into large networked tables over time
(or get moved to server back ends) and this sort of querying will really
slow things down in those environments.

A big problem is that the in-efficient query criteria is usually the easiest
to understand and takes fewer keystrokes to write so they are used a LOT and
recommended to others a LOT. It has become one of my pet topics of late to
point out the disadvantages.

 

[Dale Fye]

Thanks, guys. Appreciate the tutorial.

I'll have to go back and relook a bunch of my queries.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

So you're saying to avoid criteria like:

WHERE DateValue([DateField]) BETWEEN #1/1/07# and #12/31/07#

Why? I don't think I've ever heard that mentioned before.

The big reason is the loss of index use. If your date field has an index
then only a criteria against the field name alone will be able to utilize
that index. For example, with a numeric field even a do-nothing expression
like...

FieldName + 0

....would prevent the query engine from using the index on FieldName and
forces a full table scan.

Admittedly in most small Jet databases (particularly with local tables) the
difference would not be noticeable, but it is a bad habit to get into
because many small local tables evolve into large networked tables over time
(or get moved to server back ends) and this sort of querying will really
slow things down in those environments.

A big problem is that the in-efficient query criteria is usually the easiest
to understand and takes fewer keystrokes to write so they are used a LOT and
recommended to others a LOT. It has become one of my pet topics of late to
point out the disadvantages.