Format(DateAdd) question- can I subtract?

[Franky G]

Can anyone tell me what this does;

In the field (QBE grid)

Format(DateAdd("yyyy",3,[CASEDT]),"mmyyyy") and in the criteria field there is;

Format(DateAdd("m",2,Date()),"mmyyyy")

The field [CASEDT] Is a date field, and as far I can see, this criteria will pull results that have dates which fall two months after the date the query (or report) is run. ie run it anytime in October and I'll get results for all dates in December. I don't understand what the contents of the field are doing?

What I'm trying to do is modify this query to give me results which cover two months previous to current month. We have applications which expire - as I'm typing the penny has dropped on what the field format above does.

Right, Applications expire 3 years after [CASEDT], and this query gives me 2 months warning of applications which are about to expire. I need to have a report which lists application which have just expired. Say, everything which has expired in the last 2 months. So, report run in October, it will tell me what has expired in August AND September. Can anyone offer suggestions on how to do this?

Thanks

FrankyG

 

[AlanS]

You can use DateAdd to generate dates in the future or the past, by making the second argument either positive or negative - see the help screen for more details.

If you're trying to select records where the value of a particular field falls within the last two months, try something like this:

Field (at top of QBE grid): DateValue([field_name])

Criteria: >=DateAdd("m", -2, Date())

 

[Franky G]

Thanks,

What does DateValue function do?
I tried it and got an error about the expression being to complicated. It's a bit trial and error at the moment!

Cheers,

Frank

 

[AlanS]

The DateValue function returns a value of type Date corresponding to the date or time indicated in its argument. It's useful if you have a field or variable of type Date and you want to compare its value to a String, say "05/06/02". A Date value cannot be meaningfully compared to a String value, so you would use DateValue to convert the String into a Date whose value is what is indicated by the String. See the help system for more details.