INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using Functions in Queries

Date Arithmetic in Queries by substitute
Posted: 26 Sep 03

I answer a lot of questions regarding date arithmetic and see a lot of over-complicated answers. This may in part be due to the poor MS help documentation. This is a summary of the main answers, from which you should be able to derive most other solutions.

All examples are given as a field definition in the query grid, but other usage (criteria, update to) should be obvious!

What is a date?

Access stores date/time variables like floating point numbers where the integer part represents days since 30/12/1899 and the fractional part represents time of day.

You can use this fact to do arithmetic without any functions in many cases. You need functions if your aritmetic needs to know about the calendar or to represent the results in the way you want.

Adding/subtracting

to get a date N days before/after a given date [givendate] just use + and -:

thirtydaysago: [givendate]-30
weekstime: [givendate]+7

To get a date months or years in the past use DateAdd() as it knows how many days in each month/year:

samedaylastmonth: DateAdd("m",-1,[givendate])
samedaylastyear: DateAdd("yyyy",-1,[givendate])

You can also use DateAdd to add/subtract hours, minutes or seconds (the first argument is "n" for minutes"

DateAdd is described in the VBA help but not the Access help - open the VBA editor and look it up there

A related date

Use DateSerial() with the year(), month() and day() functions:

firstofnextmonth: DateSerial(Year([givendate]),Month([givendate])+1,1)
lastoflastmonth: DateSerial(Year([givendate]),Month([givendate]),1)-1

DateSerial works even if the month or day is <1, >12, >31 etc, it just adjusts everything logically to fit. E.g. month 0 is month 12 of the previous year.

Use DateAdd with year(), month() or day():

lastofthismonth: DateAdd("m",1,[givendate])-day([givendate])

Weeks

The function Weekday returns 1 to 7 depending of the day of the week a given date represents. This can be used with date arithmetic to give the previous Monday etc.

previousmonday: [givendate]-Weekday([givendate],3)

The second argument to Weekday() indicates the start day of the week, 1 or no argument is Sunday, 2 is Monday, 7 is Saturday.

These return true (-1) or false (0):

isworkday: weekday([givendate],2)<6
isweekend: weekday([givendate],7)<3

(thanks to Norris68 for the weekend tip)

Returning formatted dates

if you want to return a date as "Friday", "Tuesday" etc. use the Format() function:

dayname: format([givendate],"dddd")

To group records in months in a totals query add a field:

YrMnth: format([givendate],"yyyymm")

and group by that field.

Summing times

If you sum entries in a date/time field that represent times you get a numeric result, e.g. if you have

2:34
3:45
2:50

the sum comes to 0.38125. To get a H:M:S result you might use Format([mytimes],"hh:nn:ss") ("nn" for minutes not "mm" which is month) but this gives the hours modulo 24. This adds a position for the number of days if your sum is going to exceed 24 hours:

dhhmmss: Str(Int(Sum([mytimes]))) & " " & Format(Sum([mytimes]),"hh:nn:ss")

Good luck.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close