Date Difference query 1

[integritycare]

Hello,
Just have a quick question. Have searched Tek-tips posts, but was not able to find an answer.

Have three date fields, Datefrom, Dateto and DateDue, on an access form.
I wanted to use the criteria line in a query to show the difference in the datedue between the other two date fields -30days.
This would give a date due for a specific task, but give able to give the employee 30 days to rectify their issue.
I have three queries for this form using an option group. All three queries have various date requirements.
I have tried this code in the query criteria,for the Datedue field but not able to get it to work.

DateDiff("d",[Datefrom],[Dateto])-30

Where is the error please.

Regards,

Integrity

 

[PHV]

What are Datefrom, Dateto and DateDue ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi Phv
Thank you for your reply

"Datefrom" is when an employee gets a credential. "Dateto" is when it becomes due. Some credentials have 12 months before renewal, some have 2 years before renewal and some 3 years. So that is why I have used an option group so that the criteria in a query can be different.

I wanted the date due to show on the db as a date so that the employee has at least 2 months to get their credentials renewed.
So that "Datedue" should be (datedto minus 60days)
Both "Datefrom" and "dateto" has to be on the employees form for auditing purposes.
But not sure how to do this.

Many thanks,
Integrity

 

[PHV]

Something like this ?
SELECT Datefrom, Dateto, DateAdd("m",-2,Dateto) AS DateDue FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[integritycare]

Hi PHV
Many thanks for that.
Works just great...

 

[integritycare]

Hi PHV,

Just one more request.
I have noticed that the "Datedue" shows that dates - 2 months. That is great. But it also shows "datedues", for the year 2013.
Would it be possible to just show those datedues by the current date. So what I mean is that I don't want the year 2013 to become due until 2months before.
Hope I have explained myself clearly...!
Regards,
Integrity

 

[PHV]

Add a criteria, e.g.
SELECT Datefrom, Dateto, DateAdd("m",-2,Dateto) AS DateDue
FROM ...
WHERE DateAdd("m",-2,Dateto)<=Date()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886