Search through dates using like

  • Thread starter Thread starter Ripper
  • Start date Start date
R

Ripper

I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there a
way to search for dates that occured in a particular month regardless of day
or year?
 
I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there a
way to search for dates that occured in a particular month regardless of day
or year?
Click to expand...

Dates are not stored as strings, no matter what the format. You can use the
builtin functions Month([datefield]) (or Year([datefield]), Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of the
date.
 
MGFoster said:
John said:
I have a LONG table of orders. The dates are entered mm/dd/yyyy.

I was attempting to use like to search for all dates that happened in
September, regardless of year. I keep getting a syntax error. Is there
a way to search for dates that occured in a particular month regardless
of day or year?
Click to expand...

Dates are not stored as strings, no matter what the format. You can use
the
builtin functions Month([datefield]) (or Year([datefield]),
Day([datefield]),
Hour([datefield]) etc.) to extract integer values of the components of
the
date.
Click to expand...

For faster queries you can use the BETWEEN predicate:

PARAMETERS StartDate Date, EndDate Date;
SELECT ...
FROM...
WHERE date_column BETWEEN StartDate And EndDate

Using VBA functions like Month(), Year() are slower 'cuz the query
engine has to read every row in the table to evaluate the VBA function.

Be sure to index the date_column for even faster searches.
Click to expand...

Not sure that would hold try to search for "all dates that happened in
September, regardless of year."

You'd need

SELECT ...
FROM...
WHERE date_column BETWEEN #1999-09-01# And #1999-09-30#
OR date_column BETWEEN #2000-09-01# And #2000-09-30#
OR date_column BETWEEN #2001-09-01# And #2001-09-30#
OR date_column BETWEEN #2002-09-01# And #2002-09-30#
etc.
 
Back
Top