By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

BETWEEN and AND statements

P: n/a
I have a query with this Between statement as the criteria:
"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June 2004)]"
If I key in June 2002 as the first quarter date and June 2004 as the second
quarter date I get data for June 2002 upto March 2004 not June 2004. How can
I change the Between statement to include June 2004 if I enter June 2004?
TIA
Tony Williams
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@sparta.btinternet.com...
I have a query with this Between statement as the criteria:
"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June 2004)]"
If I key in June 2002 as the first quarter date and June 2004 as the second
quarter date I get data for June 2002 upto March 2004 not June 2004. How can
I change the Between statement to include June 2004 if I enter June 2004?


What kind of field is this criteria being applied to? The entry "March 2003" is
meaningless when applied to a Date field. If applied to a calculated field that
produces "mmmm yyyy" output from a Date field the resulting filter would be an
alphabetical one, not chronological.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
Sorry Rick I'm a little confused. I have the control as a date field with
the format mmmm yyyy and it seems to work as far other criteria are
concerned eg the following:
Between DateSerial(Year([Enter 1st Qtr End Date]),1,1) And [Enter 1st Qtr
End Date] Or Between DateSerial(Year([Enter 2nd Qtr End Date]),1,1) And
[Enter 2nd Qtr End Date]

Is that purely coincidence or am I missing something?
Thanks
Tony
PS Bit of an amateur so forgive the naivety!

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:30*************@uni-berlin.de...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@sparta.btinternet.com...
I have a query with this Between statement as the criteria:
"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June 2004)]" If I key in June 2002 as the first quarter date and June 2004 as the second quarter date I get data for June 2002 upto March 2004 not June 2004. How can I change the Between statement to include June 2004 if I enter June
2004?
What kind of field is this criteria being applied to? The entry "March 2003" is meaningless when applied to a Date field. If applied to a calculated field that produces "mmmm yyyy" output from a Date field the resulting filter would be an alphabetical one, not chronological.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #3

P: n/a
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@titan.btinternet.com...
Sorry Rick I'm a little confused. I have the control as a date field with
the format mmmm yyyy and it seems to work as far other criteria are
concerned eg the following:
Between DateSerial(Year([Enter 1st Qtr End Date]),1,1) And [Enter 1st Qtr
End Date] Or Between DateSerial(Year([Enter 2nd Qtr End Date]),1,1) And
[Enter 2nd Qtr End Date]

Is that purely coincidence or am I missing something?
Thanks


Well this new code example you gave is using DateSerial() to produce an actual
Date value from the parameter being entered.

Someone entering "March 2003" into a parameter provided by your first code...

"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June2004)]"

....that doesn't have DateSerial around it is just going to be treated as the
text string "March 2003".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #4

P: n/a
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
Thanks
Tony
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:30*************@uni-berlin.de...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@titan.btinternet.com...
Sorry Rick I'm a little confused. I have the control as a date field with the format mmmm yyyy and it seems to work as far other criteria are
concerned eg the following:
Between DateSerial(Year([Enter 1st Qtr End Date]),1,1) And [Enter 1st Qtr End Date] Or Between DateSerial(Year([Enter 2nd Qtr End Date]),1,1) And
[Enter 2nd Qtr End Date]

Is that purely coincidence or am I missing something?
Thanks
Well this new code example you gave is using DateSerial() to produce an

actual Date value from the parameter being entered.

Someone entering "March 2003" into a parameter provided by your first code...
"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June2004)]"

...that doesn't have DateSerial around it is just going to be treated as the text string "March 2003".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #5

P: n/a
Ive tried this
Between DateSerial(Year([Enter 1st Qtr - eg March 2003]),1,1) And [End Qtr -
eg June2004]
and still get the same problem the query doesn't return data for June 2004
Tony
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:30*************@uni-berlin.de...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cn**********@titan.btinternet.com...
Sorry Rick I'm a little confused. I have the control as a date field with the format mmmm yyyy and it seems to work as far other criteria are
concerned eg the following:
Between DateSerial(Year([Enter 1st Qtr End Date]),1,1) And [Enter 1st Qtr End Date] Or Between DateSerial(Year([Enter 2nd Qtr End Date]),1,1) And
[Enter 2nd Qtr End Date]

Is that purely coincidence or am I missing something?
Thanks
Well this new code example you gave is using DateSerial() to produce an

actual Date value from the parameter being entered.

Someone entering "March 2003" into a parameter provided by your first code...
"Between [Enter 1st Qtr - (eg March 2003)] And [End Qtr - (eg June2004)]"

...that doesn't have DateSerial around it is just going to be treated as the text string "March 2003".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #6

P: n/a
"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
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.