Text Date 20140320 to 3/20/2014 or 03/20/2014 in a Query?

[Hakala]

Hi, Tek-Tippers! I'm trying to format a text date that comes to me as 20140320 to either be a date or look like a date (I don't care which). I've searched this forum and see the CDate function but my Access 2010 doesn't recognize the commands. Either I'm using the wrong commands or I'm using it in the wrong place. Can someone give me some tips on being able to convert this date in a query?

Michelle Hakala

http://www.winebird.com/

 

[PHV]

What about this expression ?
DateSerial(Left(yourDate,4),Mid(yourDate,5,2),Right(yourDate,2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hakala]

It says "Undefined function "Left" in expression. :-(

Michelle Hakala

http://www.winebird.com/

 

[Andrzejek]

it works just fine for me.

Start new Access, and try this code:

Dim strDate As String
strDate = "20140320"
MsgBox DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))

You will get a message box with [tt]3/20/2013[/tt]

If you still have a problem with Left function, it is NOT a wrong code, it has something to do with your Access.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Hakala]

I don't know how to run that code by itself. This is why I wanted to be able to change the format in a query. Sorry....

Michelle Hakala

http://www.winebird.com/

 

[Andrzejek]

Show us your query and point where you would like to use this "20140320"

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Hakala]

SELECT Peak.QI_DATE, DateSerial(Left([QI_DATE],4),Mid([QI_DATE],5,2),Right([QI_DATE],2)) AS DateConverted
FROM Peak;

I'm just trying to get it to work, so only using the field I have and one other to get a conversion. I'm also willing to do it as a make-table.

Michelle Hakala

http://www.winebird.com/

 

[SkipVought]

Is Peak a native Access table or is it a table in some other database that you are linked to?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Hakala]

Native. Text field, 20140320.
[pre][/pre]

Michelle Hakala

http://www.winebird.com/

 

[Andrzejek]

Did you simply try:
[tt]
SELECT QI_DATE,
Mid(QI_DATE,5,2) & '/' & Right(QI_DATE,2) & '/' & Left(QI_DATE,4) AS DateConverted
FROM Peak;[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

andy, that just produces a STRING, NOT a Real Date.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Hakala]

It can be a text string and just look like a date; that's fine. I get a missing reference error on the above, so that may be my issue. "Missing or broken reference to the file 'OWC10.DLL' version 1.2." Sigh.

Michelle Hakala

http://www.winebird.com/

 

[Hakala]

Got it! I removed the reference to that .dll and now both PHV's and Andy's answers work. Thank you all so much!

Michelle Hakala

http://www.winebird.com/

 

[Andrzejek]

Skip, I know it is just a string, but Michelle stated in OP:
"20140320 to either be a date or look like a date (I don't care which)"

I think she just wants to display to the user 20140320 something that looks like a date.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Hmmmmmm? I hate fakes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

I am with you Skip: Date should be a Date, a Number should not be represented as "1234" (string), etc. But when she fixes the broken reference, she should go back to PHV's DateSerial solution.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Hakala]

I will, thank you!

Michelle Hakala

http://www.winebird.com/

 

[randy700]

If it only needs to "look like a date", why does it need to change from "20140320" to "20140320"?



Randy

 

[SkipVought]

@randy700 -- Good question. Thank You!

In the TEXT structure that you propose, it is as useless as your original yyyymmdd "date".

It will not COLLATE as a date.

It will not CALCULATE as a date.

At least the [highlight #FCE94F]yyyymmdd[/highlight] structure will COLLATE...
...AND [highlight #FCE94F]that[/highlight] CANNOT be mistaken as a REAL DATE, where as the TEXT dd/mm/yyyy CAN be mistaken as a REAL DATE!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]