Formatting Dates in a Query

[CP611]

Hi,



I've set up a make table query to change one table into another. However I'm stuck on one field, is there a way to change the way a date is formatted as part of this query? It's currently down as 2017:11:20 17:04:01 but I would like it to be like 20/11/2017, is this possible? I can format it in excel before importing, by text to columns to get rid of the time and then changing the : to a / but this is a lot of effort! If I do this before I import I can then put this is in the query. BOOKINGDATE: Format([Registration Date],"dd/mm/yyyy"). Any help would be appreciated

[JoeM]

If your entries are like "2017:11:20 17:04:01", and you just want to get the valid date out of it in a query, use this calculated field:

CDate(Replace(Left([Registration Date],InStr([Registration Date]," ")-1),":","/"))

Note that the FORMAT function returns a string, not a date. So you typically wouldn't worry about the formatting of the date in the calculation, you would handle that in the Table or Query Formatting property.

If you had wanted to return a String and not a Date, then you could wrap that whole calculation in the Format function, i.e.

Format(CDate(Replace(Left([Registration Date],InStr([Registration Date]," ")-1),":","/")),"dd/mm/yyyy")

Just note that in a Make Table query, the first calculation will set up a Date Data Type while the second will set up a Text Data Type.

[CP611]

Great the second one worked, thank you!

[JoeM]

You are welcome.