Access database presenting date in ddmmmyyyy format

I have an Access DB where its date is given in ddMMMyyyy (ex: 27SEP2020). Each time I try and set the data type as a date, it deletes the entire column of over 450 entries.

Is there a way to either allow the date format to stay as is and still use a filter on it?

UPDATE

I took some advise and ended up going with

Left([Employee_Hire_Date],2) & “/” & Mid([Employee_Hire_Date],3,3) & “/” & Right([Employee_Hire_Date],4) AS [Employee Hire Date]

Its not super pretty, but since I’m in the US, the DDMMMYYYY date type doesn’t work.

Thanks for the suggestions.

Answer

It sounds like your date is actually a string (e.g., short text).

To use it with date functions, including filtering (e.g., between 25SEP2020 and 27SEP2020) you really should convert it to a date field. You could either do this

• In a new field
• On-the-fly

For a 1-off convert, you could

1. Add a new field to the table
2. Run a query to update that field to a date based on your original field e.g., DateValue(Left([DateStr],2) & " " & Mid([DateStr],3,3) & " " & Right([DateStr],4)) (Note you may need to tweak this how it works for dates before the 10th of the month)
3. Feel free, after doing this, to delete the old field then rename the new field to the old field. Note, however, that you need to ensure any new inserts are actually inserted as dates (and if inserted as the strings, change the code to convert it to dates with the DateValue statement above)
4. For reports, if needed, you can format the field e.g., Format(yourdatefield,"ddmmmyyyy")

For doing it on the fly (which I don’t recommend), whenever you use the table you should convert all values to dates then do the appopriate tasks e.g., instead of using the field DateStr, you use the DateValue command from above.