Link to home
Start Free TrialLog in
Avatar of John Robbins
John RobbinsFlag for United States of America

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
Avatar of Norie
Norie

How are you using DateValue?

Where does the data originally come from?
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.
Avatar of John Robbins

ASKER

it comes from a linked table from excel,

New Column Name: DateValue([datefromexcel])
CDate gives me same issue,

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.
Column 1
"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.
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
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)