Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Hakala

Technical User
Apr 26, 2006
144
US
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
 
What about this expression ?
DateSerial(Left(yourDate,4),Mid(yourDate,5,2),Right(yourDate,2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
it works just fine for me.

Start new Access, and try this code:

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.
 
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
 
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.
 
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
 
Is Peak a native Access table or is it a table in some other database that you are linked to?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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.
 
andy, that just produces a STRING, NOT a Real Date.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
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.
 
Hmmmmmm? I hate fakes.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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.
 

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



Randy
 
@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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top