Changing short text field to date/time field

[fishhead]

I have a table that is updated with data provided by multiple sources that is causing problems with the date field.

I can't control how the sources format their data i can only cut and paste it into my table.

The DATE data is provided from them as follows:

source A "3/3/2020" - with field formatted as date field (= no problem)
source B "03-09-2020 (00:49)" - with field formatted as short text field


source C "2/19/20" - with field formatted as short text field.

i want to paste their data into a table with the date field set with the data field as DATE/TIME so that i can then sort by date.

Is there anyway to convert or format this data so that it can be pasted into a DATE/TIME field?
Pasting the data in to a "short text" field is what i'm doing now but doesn't allow me to sort by date (meaning show the records in descending order by date)
Pasting the data into a "date/time" field causes errors- MSaccess won't let paste the data like this.

Is there an elegant way to do this?

thanks

[ranman256]

put this function into a module, then use it in a query to convert the data into the target table.
usage:

cvtDteFld([field])

public function cvtDteFld(byval pvDte)
dim m,d,y
dim vDate as date

if isnull(pvDte) then exit function 

select case true

   case  instr(pvDte,")")>0
     m = left(pvDte,2)
     d = mid(pvDte,4,2)
     y = mid(pvDte,7,4)
     
     vDate =cvDate( m & "/" & d & "/" & y)
     
   case  isdate(pvDte) 
      vDate = pvdte
end select
cvtDteFld = vDate
end function

[fishhead]

put this function into a module, then use it in a query to convert the data into the target table.
usage:

cvtDteFld([field])

public function cvtDteFld(byval pvDte)
dim m,d,y
dim vDate as date

if isnull(pvDte) then exit function 

select case true

   case  instr(pvDte,")")>0
     m = left(pvDte,2)
     d = mid(pvDte,4,2)
     y = mid(pvDte,7,4)
     
     vDate =cvDate( m & "/" & d & "/" & y)
     
   case  isdate(pvDte) 
      vDate = pvdte
end select
cvtDteFld = vDate
end function

Thanks for the code.
I am not too comfortable with this format though, can you tell me the date/time field name you are using that the formatted data would be updated to? is it pvDte or vDate or cvDate? sorry but i don't know how to read this code to know exactly how to use it in a query

[Bullschmidt]

Thanks for the code.
I am not too comfortable with this format though, can you tell me the date/time field name you are using that the formatted data would be updated to? is it pvDte or vDate or cvDate? sorry but i don't know how to read this code to know exactly how to use it in a query

It looks like using ranman's function could work no matter what the actual field name is as it's just considered a parameter going into the function.

And here is an example of useage where the field name in the query is MyDate and the name you give to the calculated field in the query is MyDateCalc...

MyDateCalc: cvtDteFld([MyDate])

[Bullschmidt]

source A "3/3/2020" - with field formatted as date field (= no problem)
source B "03-09-2020 (00:49)" - with field formatted as short text field
source C "2/19/20" - with field formatted as short text field.

And by the way if wanted to use the built-in DateValue() function I did a quick test and noticed that these worked:
Expr1: DateValue(#3/3/2020#)
Expr2: DateValue("3/3/2020")
Expr3: DateValue("03-09-2020")
Expr4: DateValue("2/19/20")

But this caused the query to choke:
Expr3: DateValue("03-09-2020 (00:49)")

And here is a little more info about the DateValue() function...

MS Access: DateValue Function
https://www.techonthenet.com/access/.../datevalue.php

[isladogs]

I would use the CDate function

?cdate("03/09/2020")
03/09/2020 
?cdate("03-09-20")
03/09/2020 
?cdate("3/3/20")
03/03/2020 
?cdate("03-09-2020 00:49")
03/09/2020 00:49:00 

The only complication is that you need to remove the brackets using the Replace function

?cdate(Replace(Replace("03-09-2020 (00:49)","(",""),")",""))
03/09/2020 00:49:00