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 quickly. To begin, you will create a new field in your query, and then continue with the conversion below that applies in your scenario. 

Helpful 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.

Please feel free to contact me or comment if there are other short tutorials/how-to articles you would like to see. I’d love to help you out.

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Subscribe for more great content!