Access Date Problem

jjc9809 · November 9th, 2007, 01:30 PM

I have a date in Access which is coming in as 10012007 without the slashes. I need for the computer to place the slashes between the date as 10/01/2007. Any quick way to do this in a query?

Thanks

jjc9809

Joel Carmack

firefighter2045 · November 9th, 2007, 01:34 PM

What is the property field set at in your query? If it is not set to short date try setting it to that.

jjc9809 · November 9th, 2007, 01:37 PM

The date column called Transdt is coming in from the Mainframe and formatted as Text 10012007

Joel Carmack

mmcdonal · November 9th, 2007, 02:03 PM

How are you taking this information in? You can always use regular expressions if you know the sending computer is going to preserve leading zeros:

Dim dtDate As Date
Dim sDateString As String

sDateString = "10012007"

'Assuming this is October 1, 2007

dtDate = Left(sDateString, 2) & "/" & Mid(sDateString, 3, 2) & "/" & Right(sDateString, 4)

etc.

Gotta love ETL.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com