Tag: convert date

Convert Date in Access – Serial Number, Text String, First of Month, End of Month, etc.

Posted on by bonbon

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month, Date to Text. It is fairly common when compiling date data that we need to convert it, whether for use in other apps (like Excel), outputting in a nicely readable format (Word, PDF, etc.) or to enable joins to other tables, possibly even in other systems. 

MS Access will not automatically convert dates into different types from a menu, so I’ve created this handy post to quickly and easily assist you with doing so quickly.

INSTRUCTIONS

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. In the images, the date in my table is called Journal_Date, so this is the one I am converting in each scenario. You should use the actual field name in your table.

To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:

Cdbl ([yourdate])

convert date in access to serial

To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:

Cdate ([yourdate])

convert date in access - serial to date

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)

Access first of month conversion

To convert a date to the last day of the month/end of month:

DateSerial (year([yourdate]),month([yourdate])+1,1)-1

Access end of month conversion; Access last date of month conversion

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.

Access Date to Text Format Convert Date in Access

More information on the Format function and syntax can be found on the Microsoft Office Support site if you are looking to output the text differently.

Conclusion

Please feel free to contact me or comment if there are other 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!