Query Records Between Dates

[Pete]

I wish to be able to filter records between the dates of 01/07/2007 to
30/06/2008 this year which I have done by using the Between
Dateserial(Year(Date(),07,1) And Dateserial(Year(Date())+1,06,30)

This is fine until Date() becomes 01/01/2008 Can anyone give me some
criteria that will always filter records between 01/07/ this year and
30/06/ next year automatically without anyone having to type a date
in.

Thanks

Pete

 

[Arvin Meyer [MVP]]

For querying you must use the date format: m/d/yy (or mm/dd/yyyy) For your
query, I'd use the following expression in the criteria:

Between #1/1/07# And #6/30/08#

 

[Douglas J. Steele]

So am I correcting in assuming that you want to seach for dates between 1
July, 2007 and 30 June, 2008 as long as the current date is between those
dates, and that as soon as it's 1 July, 2008, you want to search for dates
between 1 July, 2008 and 30 June, 2009?

Try:

DateSerial(Year(Date()) - IIf(Month(Date()) < 7, 1, 0), 7, 1) And
DateSerial(Year(Date()) + IIf(Month(Date()) >= 7, 1, 0), 6, 30)



Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Pete]

So am I correcting in assuming that you want to seach for dates between 1
July, 2007 and 30 June, 2008 as long as the current date is between those
dates, and that as soon as it's 1 July, 2008, you want to search for dates
between 1 July, 2008 and 30 June, 2009?

Try:

DateSerial(Year(Date()) - IIf(Month(Date()) < 7, 1, 0), 7, 1) And
DateSerial(Year(Date()) + IIf(Month(Date()) >= 7, 1, 0), 6, 30)

Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)







- Show quoted text -

Brilliant, exactly what I wanted. Thank you

 

[Jamie Collins]

For querying you must use thedateformat: m/d/yy (or mm/dd/yyyy)

"Must"? I use the format yyyy-mm-dd hh:nn:ss and it works well.

For your
query, I'd use the following expression in the criteria:

Between #1/1/07# And #6/30/08#

I'd use:

BETWEEN #2007-01-01 00:00:00# AND #2008-06-30 23:59:59#

Note that yours would fail to include values between #2008-06-30
00:00:01# and #2008-06-30 23:59:59# inclusively i.e. nearly a day's
worth.

Jamie.

--

 

[Arvin Meyer [MVP]]

You are absolutely correct if the data in the date field includes time. All
date fields include the time, but if not supplied, it is considered to be
00:00 which will still get the data for the last day in the statement:
Between #1/1/07# And #6/30/08#

You may have noticed that the first post only included a reference to the
date.

 

[Jamie Collins]

You are absolutely correct if the data in the date field includes time.

Access/Jet has but one temporal data type named DATETIME. As the name
suggests, DATETIME values *always* have a 'time' element, even when
your form's control is formatted to hide it. To convey this important
point I suggest always writing DATETIME values out in full and,
considering this is an international group, a format as close to ISO
4217 as Access/Jet allows is preferred i.e.

#yyyy-mm-dd hh:nn:ss#

Jamie.

--