Query Formula with Dates

[Mudbutt]

I have two columns in a query pulling from a table right now, one is a list of material numbers and another is for dates. I need to add a third column that is pretty much an if statement saying if the corresponding date is >= -2 months ago from today's month, then show the actual date from the date column, otherwise show text "No".

It has to be the month in it's entirety instead of like -60 days or something because I need to pick up everything from two months ago forward.

I'm new to Access and struggling with formulating this. Can anyone help?

 

[boblarson]

If I read your requirements right, something like:

NewFieldNameHere: IIf(Month(Date()) - Month(Nz([OriginalFieldNameHere],0)) >= 2, Cstr(Nz([OriginalFieldNameHere],0)), "No" )

 

[Mudbutt]

This formula is only showing the date from 2 months ago, and not everything forward as well. I believe it needs to take into consideration the year because i have stuff in there with dates in 2012.

 

[boblarson]

This formula is only showing the date from 2 months ago, and not everything forward as well. I believe it needs to take into consideration the year because i have stuff in there with dates in 2012.

Okay, how about:

NewFieldNameHere: IIf(DateSerial(Year([OriginalFieldNameHere], Month([OriginalFieldNameHere]), 1) > = DateSerial(Year(DateAdd("m", -2, [OrigianalFieldNameHere])), Month(DateAdd("m", -2, [OrigianalFieldNameHere])+1), 0), Cstr(Nz([OriginalFieldNameHere],0)), "No" )

 

[Mudbutt]

Hey Bob, it's giving me an error when i input it saying there is a comma or backet out of place somewhere. I'm having trouble identifying it.

 

[Mudbutt]

Sorry, it says wrong number of arguments.

 

[boblarson]

Sorry, I missed a paren - I THINK this should do it:

NewFieldNameHere: IIf(DateSerial(Year([OriginalFieldNameHere]), Month([OriginalFieldNameHere]), 1) > = DateSerial(Year(DateAdd("m", -2, [OriginalFieldNameHere])), Month(DateAdd("m", -2, [OrigianalFieldNameHere])+1), 0), Cstr(Nz([OriginalFieldNameHere],0)), "No" )

 

[Mudbutt]

It's just coping all the dates over now, the "No" isn't being shown at all. By the way, i really appreciate your efforts with this. It's been a tough one for me to figure out too.

 

[Mudbutt]

I figured it out with your dateadd stuff. I had never used that function before.

NR: IIf([Date]>=DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)),[Date],"No")