Filter by Date Query

[DD]

I need help designing two queries. The structure of my
date field is mm/yyyy

Query 1: I need to have only the records returned within
the same calendar year. So since we are in July, I need to
see all records only for 01/2004 through 7/2004. And if it
were December I would need the records grouped for each
calendar year.

Query 2: I need to have 12 months returned. So since we
are in July I will need to see 08/2003 through 7/2004.
When we are in November I will need 12/2003 through
11/2004.

I believe Query 2 will consist of a formula -11 but I
cannot figure out how to do this.

Thanks in advance.

 

[Michael Keating]

Hi,

It's obviously the where conditions that are giving you furiously to scratch
your head, huh

I'm going to assume that you've kept the field as a date type .. as the
output format can then be changed to whatever you want.

I think for query 1 you've used something like:

WHERE Year([DateField]) = Year(Date())

For query 2, I would use:

WHERE ((Year([DateField]) = Year(Date())-1) AND
(Month([DateField])>Month(Date())))
OR ((Year([DateField]) = Year(Date())) AND
(Month([DateField])<=Month(Date())))

This is not tested, but I think it'll do what you need it to.

HTH

MFK.

 

[John Spencer (MVP)]

For the first:

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),Month(Date())+1,0)

For the second
Field: YourDateField
Criteria: Between DateSerial(Year(Date())-1,Month(Date())+1,1) AND
DateSerial(Year(Date()),Month(Date())+1,0)

If YourDateField is not a datefield, but a text field that is storing only the
month and year, then this gets a bit more complex.