VBA: Time and Date Functions

=========
QUESTION:

How do I add date  and/or time values to Access queries, VBA, etc…?

 

=========
EXAMPLES:
———
Day of the week of a given Date:
Day: Weekday([TransDate])
Day:
IIf(Weekday([TransDate])=1,"Sun", IIf(Weekday([TransDate])=2,"Mon", IIf(Weekday([TransDate])=3,"Tue", IIf(Weekday([TransDate])=4,"Wed", IIf(Weekday([TransDate])=5,"Thu", IIf(Weekday([TransDate])=6,"Fri", IIf(Weekday([TransDate])=7,"Sat","nada")))))))

———
The current month:
DateSerial(Year(Date()), Month(Date()), 1)

The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)

The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)

The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)

The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)

The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

———
The last day of the current week:
Date() - WeekDay(Date()) + 7

EOW: [TransDate]-Weekday([TransDate])+7

———
The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1

The last day of the current week:
Date() - WeekDay(Date(), 0) + 7

———
Today minus 60 days:
Now()-60

———
Date and Time Function List
REF: http://www.classanytime.com/mis333k/sjdatetime.html

Now    
Current date and time. Example: 7/5/00 3:16:38 PM returned by Now

Date
Current date only. Example: 7/5/00 returned by Date

Time
Current time only. Example: 3:12:38 PM returned by Time

Timer
Number of seconds since midnight. Example: 3:16:38 PM returned by Timer

TimeValue()    
Time part of argument. Example: 3:16:38 PM returned by TimeValue(Now)

DateValue()    
Date part of argument (excellent for ordering by date)
Example: SELECT * from tblPeople ORDER BY DateValue(Review)

DateSerial()
Date part of three arguments: year, month, day
Example: HAVING InvoiceDate <= DateSerial(Year(Now), Month(Now)-1, Day(Now))
DateSerial handles January correctly in the above example

DatePart()
Returns a portion of the date.
Year example: 2000 returned by DatePart(‘yyyy’, Date)
Month example: 10 returned by DatePart(‘m’, #10/11/2001#)
Week of year example: 41 returned by DatePart(‘ww’, #10/11/2001#)
Day of Week example: Monday returned by DatePart(‘dddd’, #6/3/2002#)
Quarter example: 4 returned by DatePart(‘q’, #10/11/2001#)

Year()
Returns the year portion of the date argument. Also see DatePart() above.

Month()
Returns the month portion of the date argument. Also see DatePart() above.

Day()
Returns the day portion of the date argument. Also see DatePart() above.

MonthName()    Used to format month names.
July returned by MonthName(Month(Date))

WeekdayName()
Used to format day names.
Wednesday returned by WeekdayName(Weekday(Date))

Today()
Current date only; used in Excel, not available in Access

DateDiff()
Returns the difference in dates.
Days example: -656 returned by DateDiff(“d”, #10/11/2001#, #12/25/1999#)
Months example: 1 returned by DateDiff(“m”, #8/10/2000#, #9/14/2000#)
Days example: 0 returned by DateDiff(“m”, date1, date2)
0 is returned above only if the two dates have same month and year

DateAdd()
Add and subtract dates.
10/11/2002 returned by DateAdd(“yyyy”, 1, #10/11/2001#))
Today’s date + 30 days returned by DateAdd(“d”, 30, Date)
The date 45 days ago returned by DateAdd(“d”, -45, Date)
To find Monday of a week: DateAdd(“d”,-WeekDay(Date)+2,Date)

Format()
Very useful for formatting dates. Examples:
Wed, 5-July-2000 returned by Format(Date,”ddd, d-mmmm-yyyy”)
5-Jul-00 returned by Format(Date,”d-mmm-yy”)

———
Find someone’s age

DateDiff(“d”,[BIRTHDATE],Date())/365)

 

=========
APPLIES TO / KEY WORDS
———
VBA
Format Date
Date Format
Date and time
Time Function
Now function
Format
Microsoft Access
Dates

 

=========
REF:

http://support.microsoft.com/kb/210604

http://office.microsoft.com/en-us/access-help/weekday-function-HA001228933.aspx?CTT=3 

http://office.microsoft.com/en-us/access-help/format-date-and-time-values-HP001099015.aspx 

http://office.microsoft.com/en-us/access-help/datediff-function-HA001228811.aspx

http://www.access-programmers.co.uk/forums/archive/index.php/t-14577.html

http://www.techonthenet.com/access/functions/date/format.php

http://www.access-programmers.co.uk/forums/showthread.php?t=40169


http://www.anysitesupport.com/vba-time-and-date-functions/ 
http://www.anysitehosting.com/

By | 2011-03-28T09:27:22+00:00 March 28th, 2011|Access, Computers, Documentation, Microsoft, VBA|0 Comments