simple date calcs

[pickslides]

Hi guys I have a query that is calling a Module to find the most recent date out of three in each record. The 3 dates are across 3 fields.

PlumberInspection 1Inspection 2Inspection 3textdatedatedate

My Query runs to return the most recent date

PlumberInspection 1Inspection 2Inspection 3Most Recent Inspection:Greatest Value(Inspection 1,....)textdatedatedateCalls on Module

Now I want to find out how many days its been between today and the most recent Inspection Date.


In the same query I now have

PlumberInspection 1Inspection 2Inspection 3Most Recent InspectionDays since Last Inspection:date()-[Most Recent Inspection]textdatedatedateCalls on Module

But this returns an error! Anyone know why?

 

[twoplustwo]

Look up the DateDiff function

 

[MSAccessRookie]

Hi guys I have a query that is calling a Module to find the most recent date out of three in each record. The 3 dates are across 3 fields.

PlumberInspection 1Inspection 2Inspection 3textdatedatedate

My Query runs to return the most recent date

PlumberInspection 1Inspection 2Inspection 3Most Recent Inspection:Greatest Value(Inspection 1,....)textdatedatedateCalls on Module

Now I want to find out how many days its been between today and the most recent Inspection Date.

A Date Field cannot be treated like a number where addition of subtraction is concerned. The Function DateAdd() and DateDiff() were designed to handle these situations.

In the same query I now have

PlumberInspection 1Inspection 2Inspection 3Most Recent InspectionDays since Last Inspection:date()-[Most Recent Inspection]textdatedatedateCalls on Module

But this returns an error! Anyone know why?

[B]DateDiff([/B][I]interval[/I][B],[/B] [I]date1[/I][B], [/B][I]date2[/I][B])[/B]
[B]DateAdd([/B][I]interval[/I][B], [/B][I]number[/I][B], [/B][I]date[/I][B])[/B]
 
NewDate = [B][COLOR=#ff0000][COLOR=black]DateDiff([/COLOR][/COLOR][/B][COLOR=black]"d"[B],[/B] StartDate[B],[/B] EndDate[B])[/B][/COLOR]
[COLOR=#ff0000][COLOR=black]NewDate = [B]DateAdd[/B][/COLOR][/COLOR][COLOR=black][B]([/B][/COLOR]"[COLOR=black]d"[B],[/B] 1[B],[/B] "OldDate"[B])[/B][/COLOR]
 
[COLOR=black][FONT=Arial][B]Where interval is one of the following:[/B][/FONT][/COLOR]
[B][FONT=Arial][/FONT][/B] 
[FONT=Arial]
[COLOR=black][FONT=Courier New][B]yyyy - Year            q    - Quarter [/B][/FONT][/COLOR]
[COLOR=black][FONT=Courier New][B]m     - Month          y     - Day of year [/B][/FONT][/COLOR]
[COLOR=black][FONT=Courier New][B]d     - Day            w     - Weekday [/B][/FONT][/COLOR]
[COLOR=black][FONT=Courier New][B]ww    - Week of year   h     - Hour [/B][/FONT][/COLOR]
[COLOR=black][FONT=Courier New][B]n     - Minute         s     - Second[/B][/FONT][/COLOR]
[/FONT]
 
[COLOR=black][FONT=Arial]The following might work in your case:[/FONT][/COLOR]
 
[COLOR=black][FONT=Arial]NewDate = [B][COLOR=#ff0000][COLOR=black]DateDiff([/COLOR][/COLOR][/B][COLOR=black]"d"[B],[/B] Date()[B],[/B] [Most Recent Inspection][B])[/B][/COLOR][/FONT]
 
 
[/COLOR]

 

[MSAccessRookie]

I see that twoplustwo has already suggested datediff(). Use that one and come back if you have any questions.

 

[pickslides]

Sensational!

datediff() worked a treat. Thanks twoplustwo & MSAccessRookie.