John Robbins
asked on
Access Query: Text to Date
Hello i am building an access query where my source has dates stored as text that come in random formats. 2/2/17, 2/2/2017, 02/02/17, etc. it is always month/day/year though. Ive tried using DateValue() but i get a "Data type mismatch in criteria expression:
I need a formula to reformat into a recognizable DateValue, Ive used something like this before MID(F2,5,2) & "/" & RIGHT(F2,2) & "/" & LEFT(F2,4) but this only works for yyyymmdd
I need a formula to reformat into a recognizable DateValue, Ive used something like this before MID(F2,5,2) & "/" & RIGHT(F2,2) & "/" & LEFT(F2,4) but this only works for yyyymmdd
If the dates are always mdy order with slashes between, then you can use cDate(F2) tp cpmvert tp a real datetime data type. If there is more variation, you may have to create a function and try to guess what the date is.
ASKER
it comes from a linked table from excel,
New Column Name: DateValue([datefromexcel])
New Column Name: DateValue([datefromexcel])
ASKER
CDate gives me same issue,
Could the issue be that there other text in some columns like "recieved" or "canceled"?
Could the issue be that there other text in some columns like "recieved" or "canceled"?
Excel allows cells to contain anything regardless of what they are defined as.
I'm assuming that the linked table shows a text format for the date column. Otherwise you would be loosing dates.
Run a query that groups by the field and counts the instances of each value. That should give you some idea of the range of values you have to deal with. If cDate() can't recognize the string as a date, you're going to have to write your own function to do it and you need to start by understanding what your challenge is. Post the results of the totals query and we'll see if we have any ideas.
I'm assuming that the linked table shows a text format for the date column. Otherwise you would be loosing dates.
Run a query that groups by the field and counts the instances of each value. That should give you some idea of the range of values you have to deal with. If cDate() can't recognize the string as a date, you're going to have to write your own function to do it and you need to start by understanding what your challenge is. Post the results of the totals query and we'll see if we have any ideas.
ASKER
Column 1
"MAT"
"Unknown"
Multiple dates
Im thinking something like IIF( contains "/", Cdate(column1), null) but what do i use instead of contains?
"MAT"
"Unknown"
Multiple dates
Im thinking something like IIF( contains "/", Cdate(column1), null) but what do i use instead of contains?
There is no internal function that will deal with this mess. You need to write your own and decide what to return if you can't find a valid date.
ASKER
This is what i am playing around with
1st day_Out: CDate(IIf([Current Cases Ext]![1st day_Out] Like "*/*",([Current Cases Ext]![1st day_Out]),"01/01/1900"))
however i would prefer the false statement to be blank but when i try to use Null but i get an Null error
1st day_Out: CDate(IIf([Current Cases Ext]![1st day_Out] Like "*/*",([Current Cases Ext]![1st day_Out]),"01/01/1900"))
however i would prefer the false statement to be blank but when i try to use Null but i get an Null error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it thanks for the help PAT I had forgotten about Cdate
1st day_Out: IIf([Current Cases Ext]![1st day_Out] Like "*/*",CDate([Current Cases Ext]![1st day_Out]),Null)
1st day_Out: IIf([Current Cases Ext]![1st day_Out] Like "*/*",CDate([Current Cases Ext]![1st day_Out]),Null)
Where does the data originally come from?