"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@hercules.btinternet.com...
So should I change the parameter to use DateSerial()?So it would become
Between DateSerial(Year([Enter 1st Qtr - (eg March 2003)] ,1,1) And
DateSerial(Year[End Qtr - (eg June2004)] ,1,1)
Sorry to be so slow on the uptake here, I would really like to understand
the difference
Your table has Date fields and will only understand comparisons to other date
values. The query...
SELECT * FROM SomeTable
WHERE DateField BETWEEN "March 2004" AND "June 2004"
.... will not work because "March 2004" is NOT a date it is a String. A person
can see that it represents a month and year but Access cannot.
You need a Query that will evaluate to...
SELECT * FROM SomeTable
WHERE DateField BETWEEN #03/01/2004# AND #06/30/2004#
Then you are comparing Dates to Dates. If you want your user to only have to
provide the month and year then your expression has to take those entries and
STILL end up with something that Access will know is a Date.
I prefer DateSerial, but you would have to feed it three separate arguments The
year and month would have to be provided with separate parameters and they would
all have to be numbers (you couldn't use "March" for example. CDate() can take
a string and convert it to a date and if it's not provided it will assume 1 for
the day.
SELECT * FROM SomeTable
WHERE DateValue
BETWEEN CDate([Enter 1st Qtr - (eg March 2003)])
AND CDate([End Qtr - (eg June 2003)])
However; the above will evaluate to...
SELECT * FROM SomeTable
WHERE DateField BETWEEN #03/01/2004# AND #06/01/2004#
Since June 1st is what is evaluated you will not actually get any records for
June unless they occur on June 1st at midnight. So you have to add an extra
month to the ending value.
SELECT * FROM SomeTable
WHERE DateValue
BETWEEN CDate([Enter 1st Qtr - (eg March 2003)])
AND DateAdd("m", 1, CDate([End Qtr - (eg June 2003)]))
You would really be better off dropping the self prompting parameter query and
feeding the criteria from a form. Self-prompting parameter queries are only
appropriate in the simplest of cases and are almost never used in a production
quality application.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
