query part of date using a parameter query

[Guest]

I want to setup a parameter query...the problem arises in that the field that
I want to query also has a time stamp ie: 5/4/2004 6:31:43 AM...in the
criteria row of the query design window I have the square brackets[]...when
the query prompts you what syntax would I use?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A couple of ways:

1) Use the DateValue() function to pull the date only:

WHERE DateValue(date_column) = #5/4/2004#

This method is slow, 'cuz the query has to scan the whole table,
translating the date_column to just the date value.

2) Use a date range:

WHERE date_column BETWEEN #5/4/2004# and #5/4/2004 23:59:59#

Note that the date range is only one day: #5/4/2004# is really

5/4/2004 00:00:00 midnight of 5/3 the starting time of 5/4.

To get the complete day you have to have an ending date/time that
includes the time at 1 second before midnight of 5/4.

Method 2 will be faster than method 1, especially if the "date_column"
is indexed, 'cuz the query will use the index & find only date values
that satisfy the criteria. BTW, the end date value is how you put date
and time values in the QBE criteria grid. Use 24 hour time to avoid
using AM/PM.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj+bsIechKqOuFEgEQIPZwCgiNNjDmaXZki4WPj4F0FkAhGGEa4AoLps
PQj4Wt/Gu0/uQ0IBHi/NjY4P
=gH25
-----END PGP SIGNATURE-----

 

[Marshall Barton]

I want to setup a parameter query...the problem arises in that the field that
I want to query also has a time stamp ie: 5/4/2004 6:31:43 AM...in the
criteria row of the query design window I have the square brackets[]...when
the query prompts you what syntax would I use?

If you want to search for only the Date portion of the
field, then use a calculated field with the expression:
DateValue([datefield])
so you can use just the date in the prompt box. The prompt
value you enter can then be any legimate date, such as
3/21/05, 21 March 2005, 2005-3-21, . . .