Hi,

I'm trying to do query that will return all rows within the last quarter of
any year.
I've tried: "Between #10/1/????# And #12/31/????#" and I get an error
message:
"The expression you entered has an invalid date value."

If I run "Between #10/1/1995# And #12/31/1995#", it works fine, but for 1995
only.
I need to be able to return the last quarter of all years.

Any suggestions?

Thanks,
Mark.

Try Format([MyDate],"q") and then set the criterion 4

"Mark" <m.misener (AT) sympatico (DOT) ca> wrote

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

You could use the DatePart() function to get the quarter. E.g.:

SELECT ...
FROM ...
WHERE DatePart("q",Date_column) = 4

See the Access VBA help article "DatePart Function" for more info.

You could also use query parameters to have the user enter the quarter
they want. E.g.:

PARAMETERS [What quarter?] Byte;
SELECT ...
FROM ...
WHERE DatePart("Q", Date_column) = [What quarter?]

See the Access Help article "About parameter queries that prompt for
criteria" for more info.

Putting the DatePart() function on the left side of the equation in a
query's WHERE clause can slow down the run. That's because the query
will have to scan the whole table & convert the date to a quarter value.
If you want to improve the query's speed you can create a new, indexed
column in the table that holds the date's quarter. Then use the
quarter_column in the WHERE clause:

....WHERE quarter_column = 4

That's really a design/performance decision.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQa1FNYechKqOuFEgEQLHpwCdFTreHqKDY/6+kTTds/gDtfvosOIAoPQf
jNJNjIbmwbhCKCDwsPRlQMYM
=a2el
-----END PGP SIGNATURE-----


Mark wrote:

"Mark" <m.misener (AT) sympatico (DOT) ca> wrote

Thanks for the advice everyone.
I've decided to make use of the DatePart("q",[OrderDate])=4 option.

Mark.