Help with calculating 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.

The first three columns of my query are AssetID, Acq Date, Depr Start Date.

I inserted 2 more columns - The first showed the month of the Acq date: AcqDate: Month([Fixed Assets]![Acq Date])

The second column was the month of the Depr Start Date: MonthDepr: Month([Fixed Assets]![Dep Start Date])

In the next column I calculated the difference between the two with the following: Difference: ([MonthDepr])-([AcqDate])

I only want to see items <> 1 or <> -11 but when I put that in the criteria the query asks me to Enter a Parameter value. I don't want to enter a value so what am I doing wrong. I only want to see records where the difference between the two months is <> 1 or <> -11. I'm sure it's simple but it's driving me crazy.

 

[lespaul]

Switch to the SQL view of the query and post that here.

If you get the criteria set correctly it will work. Should be something like:

WHERE Month([Fixed Assets]![Dep Start Date]) - Month([Fixed Assets]![Acq Date]) <> 1 OR Month([Fixed Assets]![Dep Start Date]) - Month([Fixed Assets]![Acq Date]) <> -11

Leslie

 

[crystalfun]

Please let me know where my mistake is - Thanks

SELECT [Fixed Assets After Exchange Calc].[Asset ID], [Fixed Assets After Exchange Calc].[Acq Date], [Fixed Assets After Exchange Calc].[Dep Start Date], Month([Fixed Assets After Exchange Calc]![Acq Date]) AS AcqDate, Month([Fixed Assets After Exchange Calc]![Dep Start Date]) AS MonthDepr
FROM [Fixed Assets After Exchange Calc]
WHERE (((([MonthDepr])-([AcqDate]))<>1 Or (([MonthDepr])-([AcqDate]))<>-11))
GROUP BY [Fixed Assets After Exchange Calc].[Asset ID], [Fixed Assets After Exchange Calc].[Acq Date], [Fixed Assets After Exchange Calc].[Dep Start Date], Month([Fixed Assets After Exchange Calc]![Acq Date]), Month([Fixed Assets After Exchange Calc]![Dep Start Date]);

 

[BobJacksonNCI]

For starters, to just handle the <> 1 month difference, replace your WHERE clause with this:

WHERE  MONTH([Fixed Assets After Exchange Calc].[Dep Start Date]) <> MONTH(DATEADD("m",1,[Fixed Assets After Exchange Calc].[Acq Date]))

Once that works, post back with your additional requirements.



HTH,
Bob