access

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » Access 2003

Access 2003

resolvedResolved · Low Priority · Version 2003

George has attended:
Access Advanced course
Access VBA course

Access 2003

I can make a calculation of days ( In this case 'ageatdeath' by using a macro to subtract 'dateofdeath' from 'dateofbirth' - how do I make this show in days, weeks, months, years etc rather than simply in days?

Edited on Sat 7 Jun 2008, 23:34

RE: Access 2003

Hi George,

Thanks for the post, I would probably use the DateDiff rather than a Macro, although you could incorporate it into VBA code or a SQL query, here are a couple of examples, you can substitute your own field names:

In Access, the DateDiff function returns the number of time intervals between two dates.
The syntax for the DateDiff function is:
DateDiff ( interval, date1, date2[, firstdayofweek[, firstweekofyear]] )

You can use the DateDiff function to determine how many time intervals exist between two dates. For example, you can use DateDiff to calculate the number of days between an order date and its shipdate or the number of weeks between today and the end of the year.
Interval is the interval of time to use to calculate the difference between date1 and date2. The following is a list of valid interval values.

Interval Description
yyyy Year
q Quarter
m Month
y Day of year
w Weekday
ww Week
h Hour
n Minute
s Second

date1, date 2 are the two dates to calculate you want to use in the calculation.

firstdayofweek is optional argument that specifies the first day of the week. If not specified, Sunday is assumed. If not specified, the first week is assumed to be the week in which Jan 1 occurs.

Examples

DateDiff ('m', #12/17/2003#, #01/22/2004# ) returns 1
DateDiff ('yyyy', #11/05/2001#, #11/10/2003# ) returns 2

VBA Code

Dim MyNumber As Integer
MyNumber = DateDiff('d',#10/06/1998#,#12/09/1998#)

This example uses the DateDiff function to display the number of days between a given date and today. Now the MyNumber variable would contain the value of 64.

SQL query

You can also use the DateDiff function in a query.
SELECT DateDiff('d',#05/05/1985#, Now()) AS Expr1
FROM Orders

I hope you can dig something useful from this lot;
best regards Pete

Edited on Sat 7 Jun 2008, 23:38

RE: Access 2003

Sorry pressed submit twice


 

Access tip:

Add current time to a field

Press CTRL+SHIFT+ SEMICOLON

View all Access hints and tips


Server loaded in 0.08 secs.