Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month. MS Access will not automatically convert dates into different types, so this post will assist you with doing so manually. You will create a new field in your query to accomplish this.
Hint: Substitute [yourdate] with the name of the field that you are converting.
To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:
Cdbl ([yourdate])
———————————–
To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:
Cdate ([yourdate])
———————————–
To convert a text or string date to the date/time format, try:
DateValue ([yourdate])
———————————–
To convert a date to the first day of the month:
DateSerial (year([yourdate]),month([yourdate]),1)
———————————-
To convert a date to the last day of the month:
DateSerial (year([yourdate]),month([yourdate])+1,1)-1
———————————-
You can also convert a date/timeformat to a string – here is one example:
Format([yourdate],”mmmmyyyy”)
This would render 7/1/19 in your date field as July2019.
More information on the Format function and syntax can be found on the Microsoft Office Support site.
If I helped you, please consider buying me a coffee via PayPal! Thanks!!
Subscribe for more great content!