I still don't get it. CDate Question

[trythis]

I want to take a string 070809 for example and convert it to a date.

table name:

phonedata

field with string is Field3

I have tried so many ways I have forgotten what I tried. I cannot get it right.

should I create an update query?

Field: Field3
Table: phonedata
update To:CDate(???)

Not sure what the question marks should be nothing I tried works.

Thank you for your help.

 

[boblarson]

It would be:

Field: Field3
Table: phonedata
update To:CDate([Field3])

 

[raskew]

Hi -

Suspect you're going to have further problems with this since "070809" is not considered (at least in my regional settings) as a valid date format.

As a result, Access will interpret it as the floating point number representing a date, as stored internally by Access. Here's an example:

? cdate("070809")
11/11/2093

Here's how Access stores 11/11/2093
? cdbl(#11/11/2093#)
70809

Using a proper date format to invoke the cDate() function
? cdate("07/08/09")
7/8/2009

Bob

 

[boblarson]

Hi -

Suspect you're going to have further problems with this since "070809" is not considered (at least in my regional settings) as a valid date format.

As a result, Access will interpret it as the floating point number representing a date, as stored internally by Access. Here's an example:

? cdate("070809")
11/11/2093

Here's how Access stores 11/11/2093
? cdbl(#11/11/2093#)
70809

Using a proper date format to invoke the cDate() function
? cdate("07/08/09")
7/8/2009

Bob

Spot on as usual. I guess I should have checked first .

One way to overcome would be

DateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2))

I THINK (it works on my test).

 

[trythis]

ya I tried that

Field: Field3
Table: phonedata
update To:CDate([Field3])

It didn't work.


This looks good where do I put it

DateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2))

I tried this (it did not work)

Field: Field3
Table: phonedata
update ToateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2))

also tried
CDate(DateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2)))


Help

 

[boblarson]

ya I tried that

Field: Field3
Table: phonedata
update To:CDate([Field3])

It didn't work.


This looks good where do I put it

DateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2))

I tried this (it did not work)

Field: Field3
Table: phonedata
update ToateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2))

also tried
CDate(DateSerial(Mid([Field3],5),Mid([Field3],1,2),Mid([Field3],3,2)))


Help

How about posting the database?

 

[trythis]

Here is an example of the import file.

Here is my table I had to convert this to 2003 to make it small enough to upload. My DB is in 2007 .accdb

Let me know

 

[trythis]

This one removed the leading 0 on import

 

[boblarson]

Okay, since your month doesn't start with a zero, I modified the query but also we have to put your conversion into a separate field, but you can update the other but it will update to 40011 which is the serial value of the date.

See attached.

 

[boblarson]

This one removed the leading 0 on import

I accounted for either a leading zero or no leading zero in the modifed query in the attachment I provided in my previous post (just posted).

 

[trythis]

my system must have stripped the query because all I see is the table?

I see that it worked by the column in the table.

Tina

 

[boblarson]

my system must have stripped the query because all I see is the table?

I see that it worked by the column in the table.

Tina

Are you viewing it in 2007? If so, make sure to go to the nav pane and use the View > By Object and then select ALL objects.

 

[boblarson]

Here's the SQL of the query:

UPDATE tblPhoneData SET tblPhoneData.Field3Converted = DateSerial(Mid([Field3],5),IIf(Len([Field3])=6,Mid([Field3],1,2),Mid([Field3],1,1)),IIf(Len([Field3])=6,Mid([Field3],3,2),Mid([Field3],2,2)));

This used a new field I had created in your table called Field3Converted and it was a short date field.

 

[trythis]

EEEEUREKA!!!


I see it now.

WOW! Looks complicated.

Thank You so Much

 

[trythis]

I tried to click on add to your reputation but it said I had to share the love.

Thanks Again,
Tina

 

[boblarson]

EEEEUREKA!!!


I see it now.

WOW! Looks complicated.

Thank You so Much

It only looks complicated. But if you break down the IIF components, you'll see that I am checking to see if the length is long enough to have the leading zero and if so use the one way and if not use the other way.

 

[raskew]

Bob Larson -

You beat me to it.

Great minds must stumble along similar paths. Here's the solution I created.

SELECT tblPhoneData.Field3, DateSerial(Right([Field3],2),Left([Field3],IIf(Len([Field3])=6,2,1)),Mid([Field3],IIf(Len([Field3])=6,3,2),2)) AS Expr1
FROM tblPhoneData;

Best wishes - Bob