=========
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/