Date Functions in Access

[Guest]

Hello,
I am trying to calculate the # of months a project is past due. I have a
field "start date" and another field "est. end date". I am looking for a
function that will look at the current date and subtract it from the "est.
end date". For example, if my "est. end date" was 3/1/07 then based on
today's date the project is 8 months past due.
I am able to do this in excel by using the "DATEDIF" function, how can I do
it in Access?
Thank you.

 

[Guest]

In Access it’s the DateDiff function. The syntax is:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The interval character for months is, not surprisingly, "m". The last two
arguments are optional, defaulting to Sunday and the week containing 1
January.

Ken Sheridan
Stafford, England

 

[Ken Snell \(MVP\)]

In Access it's the DateDiff function. The syntax is:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The interval character for months is, not surprisingly, "m". The last two
arguments are optional, defaulting to Sunday and the week containing 1
January.

Just for additional information, the DateDiff function may give "unexpected"
results because of how it's used. For example using December 31, 2006 and
January 1 2007:
DateDiff("m", #12/31/2006#, #1/1/2007#)

will return a value of 1 because DateDiff subtracts based on just the month
part of the dates.


And
DateDiff("yyyy", #12/31/2006#, #1/1/2007#)

will return a value of 1 because DateDiff subtracts based on just the year
part of the dates

 

[Guest]

Thanks for the quick response. This is what I have :
DateDiff("m",[Est. Completion Date],[firstweekday],[firstweek])
When I run the query I am prompted to "Enter parameter value" for both the
firstweekday and firstweek.

In Access it’s the DateDiff function. The syntax is:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The interval character for months is, not surprisingly, "m". The last two
arguments are optional, defaulting to Sunday and the week containing 1
January.

Ken Sheridan
Stafford, England

Hello,
I am trying to calculate the # of months a project is past due. I have a
field "start date" and another field "est. end date". I am looking for a
function that will look at the current date and subtract it from the "est.
end date". For example, if my "est. end date" was 3/1/07 then based on
today's date the project is 8 months past due.
I am able to do this in excel by using the "DATEDIF" function, how can I do
it in Access?
Thank you.

 

[Guest]

That's because these are neither columns in the table, nor built in
functions, so Access doesn't recognize them and this treats them as
parameters. What you want is:

DateDiff("m",[Est. Completion Date],Date())

Note what Ken Snell says, however. The above expression returns the
difference month on month. If you want to take the day of the month for both
dates into account then you'd need to extend the expression like so:

DateDiff("m",[Est. Completion Date],Date()) - IIf(Day[Est. Completion Date])

Day(Date()),1,0)

This will compute the difference month on month, then subtract 1 if the
[Est. Completion Date] date's day of the month is after the day of the month
of the current date, e.g. as of today, #10/11/07# the following expression:

DateDiff("m",#02/11/07#,Date()) - IIf(Day(#02/11/07#) > Day(Date()),1,0)

would return 8, but the following:

DateDiff("m",#02/12/07#,Date()) - IIf(Day(#02/12/07#) > Day(Date()),1,0)

would return 7, as the 8 months difference would not be reached until
tomorrow, #10/12/07#.

Ken Sheridan
Stafford, England

Thanks for the quick response. This is what I have :
DateDiff("m",[Est. Completion Date],[firstweekday],[firstweek])
When I run the query I am prompted to "Enter parameter value" for both the
firstweekday and firstweek.

In Access it’s the DateDiff function. The syntax is:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The interval character for months is, not surprisingly, "m". The last two
arguments are optional, defaulting to Sunday and the week containing 1
January.

Ken Sheridan
Stafford, England

Hello,
I am trying to calculate the # of months a project is past due. I have a
field "start date" and another field "est. end date". I am looking for a
function that will look at the current date and subtract it from the "est.
end date". For example, if my "est. end date" was 3/1/07 then based on
today's date the project is 8 months past due.
I am able to do this in excel by using the "DATEDIF" function, how can I do
it in Access?
Thank you.