Calculating no. of days between dates

[Jayee]

Hi,

I would like to know the formulae for creating for calculating the number of calendar days between two dates which are stored in my database - ie. next service date - current date = x number of days.

As well as creating a result (for displaying on the "Servicing" form), I would like to use that "number of days" as criteria for reports so that I can print off a report showing vehicles which are due for service within the next, say 30 days.

Thanks for your help.

Regards,
Jay

 

[PHV]

And what about simply this (if really current date)?
[next service date] - Date()
Or this (if two dates which are stored) ?
[next service date] - [current date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[missinglinq]

You need to use the DateDiff function, something like this:

ElapsedDays= DateDiff("y", [CurrentDate ], [NextServiceDate])

Hope this helps!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Jayee]

Hi,

Thanks.

I've tried both of your suggestions and am still getting a "#Name?" error message.

My current date field is currently only on a table, as I only use it for calculation purposes, and I can't see any need to store it in a table. It is called "Current date" and the control source is =Now().

My next service date field is stored against each vehicle's record in the datebase and is called "Next service date" and is a date/time field which is working fine.

Can you help me further ?

Regards,
Jay

 

[Jayee]

Hi,

I've just realised I need another date calculation formulae in my datebase which I'm not sure how to do.

I think it would be good if my "next service date" was a calculated field too, as it is always 1 year on from "last service date" and that'd save me typing it in each time and reduce the risk of errors.

I would, however, want the field result stored within the database, unlike my "in x days" field which is only used for calculation.

How do I do this ?

Regards,
Jay

 

[PHV]

Have a look at the DateAdd function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tct1954]

Off the top of my head, I think the #name error is because Access can't find one of field names.

Try modifying the syntax 'missingling' supplied, replacing the tablenames and fieldnames as appropriate.

ElapsedDays= DateDiff("y", [tablename]![CurrentDate ], [tablename]![NextServiceDate])

tct1954

 

[CliffSP]

or try:

QtyDays: DateDiff("d", [OrderDate], [ServiceDate])

should give you the answer...