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!