DATEADD OR DATEDIFF? THAT IS THE QUESTION

[Angelique]

Need to create an "overdue" letter to the client. I have added an unbound textbox with the following syntax:

=Format(DateAdd("yyyy", 1, [Date of Training]), "mmmm" & "yyyy" & ".&quot

When I run the report some of the letters have July, August, September 2001 etc instead of the month + 1 year.

Also, I would like to check the syntax for my "overdue":

((DateDiff(&quot;d&quot;, Now(), [Date of Training]<-365))

Is there a better syntax??

Angelique

 

[tlbroadbent]

The first format looks good except there is no space between month and year.

=Format(DateAdd(&quot;yyyy&quot;, 1, [Date of Training]), &quot;mmmm&quot; & &quot; &quot; & &quot;yyyy&quot; & &quot;.&quot

The July, August and September dates probably show because of faulty selection criteria. I would use the following criteria to select records.

[Date of Training]<DateAdd(&quot;d&quot;,-365,Date()) Terry

http://www.defendscouting.com/

&quot;I'm not dumb. I just have a command of throughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[Angelique]

Terry,

If [Date of Training] is 01/01/2000
01/02/2000
10/03/2000

etc

Then I would expect the reports to display in the following order:

01/01/2001
01/02/2001
10/03/2001

etc

If I only select one row (client) then the report usually produces the correct result January 2001 BUT if there is more than one row (clients) then it jumps an entire year to produce February 2002.

What am I doing wrong?

Angelique

 

[Angelique]

Terry,

Don't worry I managed to resolve it - I was using an inappropriate variable which must have produced the irregular results. Once I changed it, everything seems OK.

Your other syntax [Date of Training]<DateAdd(&quot;d&quot;,-365, Date()) produces -1 & 0 instead of dates. Am I supposed to format this or doesn't it recognise it as a date?

Angelique

 

[tlbroadbent]

[Date of Training]<DateAdd(&quot;d&quot;,-365, Date()) is the selection criteria as it would appear in the SQL view not in the query designer form view. You would not place the entire phrase in the field name unless you wanted to check the boolean for true (-1) or false (0).

In the query designer form you would set the following values.

Field Name: [Date of Training]
Criteria: ]<DateAdd(&quot;d&quot;,-365, Date()) Terry

http://www.defendscouting.com/

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[Angelique]

Thanks Terry - worked fine, my only excuse is that I have had a bad cold which won't go away, must be affecting my brain.

Angelique