Query Dates.

[crystalfun]

I have a field called "Acq Date" and a second field called "Depr Start date." The Depr start date should be the first day of the first month after Acq Date.

I would like to write a query that shows me all items where the month of Depr Start Date does not equal one month after acq date. Example would be to see an item if Acq Date is 3/17/04 and Depr start date is 5/1/04. I want to see it. If Depr start date is 4/1/04 its OK and I don't want to see it. How can I write my query for this date issue?

Thanks.

 

[BobJacksonNCI]

Hi crystalfun!

Query design view: In a new column enter an expression, like: MonthDepr: Month([Depr Start Date])

In the criteria for this column, put in:
<> Month([Acq Date] + 1)

That should return rows as you requested.
Note there is no consideration for years being different.
And you indicated that Depr Start Date should be the first of the month, but this logic does not address that and it would a separate column entry to serve as an "OR" condition if you wanted to see any that aren't the first of the month in the same result set.



HTH,
Bob

 

[PHV]

Have you tried this criteria for the [Depr Start date] column ?
<> DateSerial(Year([Acq Date]), 1 + Month([Acq Date]), 1)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244