Problems with converting a text field to date field

[petern]

I am building a custom report for our organization in Access. The membership management software is in the cloud and can't give us the information we need. So I export from the online system into an Excel file and then import the Excel file into Access. The particular report I'm working with has an Invoice Date field and a Payment Date field. The data generated from the output correctly formats the Invoice Date field as a Date field and Access reads it correctly. Unfortunately, for some reason, the Payment Date field shows up in Excel as a general format and Access imports it into a text field. If I set the field in Access to be a date field, it misreads the data 10/2/2019 is being misread as February 10, not the correct Oct 2. I've tried stripping out the time stamp which comes with it (10/2/2019 11:38:32 AM) with an Update query and then using CDate to force it to a date format, but it's not working. Any suggestions on the simplest way to deal with this would be most helpful. I'd prefer to be able to handle this in Access with queries and code as needed as the process will be run by an office staffer. I'd rather she doesn't have to do some data manipulation in Excel unless it can be automated (and I have almost no experience with doing anything complicated in Excel).

Thanks,
Peter

[HansV]

What is your system date setting? dd/mm/yyyy, mm/dd/yyyy or yyyy/mm/dd ?

[HansV]

Try an update query in which the Payment Date field is updated to

DateSerial(Mid([Payment Date],InStr(InStr([Payment Date],"/")+1,[Payment Date],"/")+1,InStr(InStr(InStr([Payment Date],"/")+1,[Payment Date],"/")+1,[Payment Date]," ")-InStr(InStr([Payment Date],"/")+1,[Payment Date],"/")-1),Left([Payment Date],InStr([Payment Date],"/")-1),Mid([Payment Date],InStr([Payment Date],"/")+1,InStr(InStr([Payment Date],"/")+1,[Payment Date],"/")-InStr([Payment Date],"/")-1))

Then change the field to a Date/Time field.

Please test it on a copy of your table (or database) first!

[petern]

Thanks Hans. That worked a treat. The nice thing with this database is that its sole function is as a report generator, so there's never any data to lose! If you have time, I'd be grateful if you could unpack what the formula does. I figured I'd need to use DateSerial to do it but I never would have been able to figure this one out on my own!

For what it's worth, my system date is set to dd-mmm-yyyy. In Canada, because we are completely inconsistent with dates generally, I have long had a habit of making sure months display in words so that there is no ambiguity with end users and it's much harder for incorrect dates to be added in.

[HansV]

I built the formula in parts, then combined the parts to create the monster expression in my previous reply. It's easier to understand the parts:

p1: InStr(InStr([Payment Date],"/") is the position of the first "/" in the date+time string.
p2: InStr(p1+1,[Payment Date],"/") is the position of the second "/".
p3: InStr(p2+1,[Payment Date]," ") is the position of the first space after that "/".

y: Mid([Payment Date],p2+1,p3-p2-1) is the part between the second "/" and the space after it, i.e. the year.
m: Left([Payment Date],p1-1) is the part before the first "/", i.e. the month.
d: Mid([Payment Date],p1+1,p2-p1-1) is the part between the first and second "/", i.e. the day.

DateSerial(y,m,d) is the date you want.

[petern]