Dates

J

Jeff C

I obviously have issues with "time"....(ie. I'm losing it)

How do I change the following to return values from 12 months ago as of
todays date. The following returns January 2004 and Jan 2005.

(Between DateSerial(Year(Date()),Month(Date())-12,1) And
DateSerial(Year(Date()),Month(Date())-11,0))

For those with continued patience with me..Thank You.
 
F

fredg

I obviously have issues with "time"....(ie. I'm losing it)

How do I change the following to return values from 12 months ago as of
todays date. The following returns January 2004 and Jan 2005.

(Between DateSerial(Year(Date()),Month(Date())-12,1) And
DateSerial(Year(Date()),Month(Date())-11,0))

For those with continued patience with me..Thank You.
Click to expand...

I'm not really clear as to what you want returned.
For ALL records between the current date and exactly one year ago:
Between DateAdd("yyyy",-1,Date()) and Date()
 
J

Jeff C

Better put....when I run the expression:

(Between DateSerial(Year(Date()),Month(Date())-12,1) And
DateSerial(Year(Date()),Month(Date())-11,0))

it returns dates in January 2004 and January 2005...

I need to return dates in January 2004...Next month I want it to return
February 2004 etc....Thank You
 
D

Douglas J. Steele

DateSerial(Year(Date()),Month(Date())-12,1) definitely returns 2004-01-01
and DateSerial(Year(Date()),Month(Date())-11,0) returns 2004-01-31, so
there's something else that's causing the problem.

What's the entire SQL of your query?
 
J

Jeff C

Thanks Doug:
If you have the time....look at my Thread.."Parameter Query" just above this
thread, there is a puzzling problem somewhere....I fought with this
expression for several hours changing things one way or the other and ended
up with the example posted above.

The work my database records is all based on the relationship of a date
(recorded as mm/dd/yyyy) in each record with “Today’s Dateâ€, whenever today
is. I need to find a way of expressing the date criteria I want consistently
as we progress through the year. I obtain records based on dept, directors,
and category for the following:

1. All dates in the month 6 months ago OR All dates in the month 12 months ago
a. (Due This Month)

2. All dates in the month 5 months ago OR All dates in the month 11 months ago
a. (Due Next Month)

3. All dates older than 6 months ago this month OR All dates older than 12
months ago this month
a. (Noncompliant)

Thank You.
 
Top