Text Date to Actual Date

[gmatriix]

Hello guys,

I have not been on here in a while.

I have a issue I am trying to solve.

I have data in a database that is formatted like this:
yyyy/mm/dd ...in text format

So I did this:

DateSerial(Left([VACY_CONTE.INIT_PREM_EFF_DATE],4),Mid([VACY_CONTE.INIT_PREM_EFF_DATE],5,2),Right([VACY_CONTE.INIT_PREM_EFF_DATE],2))

Which works fine...but when I try to filter on that information I get data mismatch.....??

I tried VAL() tried DateValue() tried Cdate()....nothing is working....what am I missing????:banghead:

Any Ideas??

 

[plog]

Pretty certain not all of your data is formatted like you say--or its null or it has improper values (2017/02/31).

I would reconfigure your expression to generate a string that puts your data into a mm/dd/yyyy format, let's call that field DateTest. Save that query as TestQuery then use this SQL to find your culprit:

SELECT DateTest, IsDate(DateTest)
FROM TestQuery
WHERE IsDate(DateTest)=False

 

[Mark_]

What type of field is VACY_CONTE.INIT_PREM_EFF_DATE? If it is declared as a DATE, you should not have to worry about how it is formatted as internally it is stored as a DECIMAL.

How are you trying to filter against it?

 

[The_Doc_Man]

Mark_, technically a DATE is a typecast of a DOUBLE, not a DECIMAL.

 

[Mark_]

@Doc,

Thanks, I'll try and remember.

P.S. Terse post for you...

 

[MarkK]

On my machine the CDate() function accurately converts a string date formatted "yyyy/mm/dd" into a VBA.Date. More specifically, I can do this in the immediate pane and get this result...

? cdate("2018/03/09")
3/9/2018 
? typename(cdate("2018/03/09"))
Date

But your post is not clear. You say you are not able to "filter on that information, you get a type mismatch," but it's not clear what kind of filtering you are applying. Are you setting the filter property of a form? If so, show your form's RecordSource and the text of the filter you are applying. Are you writing a SQL WHERE clause? If so, show your SQL.
hth
Mark

 

[arnelgp]

Save the query and make another query based on the one u saved. Use the later query and you can now filter on it.

 

[The_Doc_Man]

when I try to filter on that information I get data mismatch

What EXACTLY are you using for a filter and what is the data type of the field you are attempting to use as the basis of the filter? I'm thinking that NONE of those things you mentioned should have a problem converting the date, BUT there is the issue that a filter string is a type of WHERE clause (without the word WHERE) and is therefore subject to SQL syntax rules on sub-clauses. The type mismatch might not be in the conversion of your string date to a date, but rather the next step - using said date as a filter. At least that is what it "smells like" to me.

 

[gmatriix]

What type of field is VACY_CONTE.INIT_PREM_EFF_DATE? If it is declared as a DATE, you should not have to worry about how it is formatted as internally it is stored as a DECIMAL.

How are you trying to filter against it?

This is a text field ...unfortunately....So I am trying to rearrange in the actual date format so that I can use the data to filter..

 

[gmatriix]

Pretty certain not all of your data is formatted like you say--or its null or it has improper values (2017/02/31).

I would reconfigure your expression to generate a string that puts your data into a mm/dd/yyyy format, let's call that field DateTest. Save that query as TestQuery then use this SQL to find your culprit:

SELECT DateTest, IsDate(DateTest)
FROM TestQuery
WHERE IsDate(DateTest)=False

Yes...you are right! I think that is why I am getting the error....it was weird because I would run on query with a date....then changed the date and I would get a data mismatch....I was like...huh???

That make sense....what is the best way to right this?

 

[JHB]

Only for finding the problem, create a new "Make Table" query, and put that into it, then look if the dates is okay, (also look at the table in design view it the field actually is created as a date type field).

DateSerial(Left([VACY_CONTE.INIT_PREM_EFF_DATE],4),Mid([VACY_CONTE.INIT_PREM_EFF_DATE],5,2),Right([VACY_CONTE.INIT_PREM_EFF_DATE],2))

 

[gmatriix]

Only for finding the problem, create a new "Make Table" query, and put that into it, then look if the dates is okay, (also look at the table in design view it the field actually is created as a date type field).

Yeah....thanks....I did do that and found that there are errors...Now I am trying to write this so I don't get error.....

Open for Ideas...

Thanks

 

[gmatriix]

Thank guys!

You guys are awesome!

My data was the problem. There was some data in the record that was not good...so it threw it off....Once I accounted for the bad data...it worked just fine...

Thanks again!!!