1. Registered User
Join Date
Feb 2004
Posts
214

I have a file that I import into my db. The date comes across as 1070710 for 07/10/2007. The 107/ refers to the year, the next /07 is the month, and the last /10 is the day.

How can I format this date to show 7/10/2007?

2. Computer Monkey
Join Date
May 2005
Posts
1,191
Can you build it from a string? Assuming this is the format it always comes in, do like...
Code:
```Dim dateX As Date, strX As String
strX = "1070710"
dateX = CDate(Mid(strX, 4, 2) & "/" & Right(strX, 2) & "/" & Mid(strX, 2, 2))```
This is assuming the data always comes in as you set out in your rules.

EDIT:
I'm not sure how you're date will work when you get to the year 2100 or for any date back in the 20th century...

3. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
To carry it further, if "107" is the year, it's counting from 12/31/1899. 1/1/1900 would come across as "0000101". Ergo, let's extend Nick's code:

Code:
```Dim dateX As Date, strX As String, lYear As Long
strX = "1070710"
lYear = Val(Left(strX,3)) + 1900
dateX = CDate(Mid(strX, 4, 2) & "/" & Right(strX, 2) & "/" & lYear)```
Sam

4. Super Moderator
Join Date
Jun 2004
Location
Arizona, USA
Posts
1,848
And, it probably wouldn't hurt to import the data into a temporary file first, then run a table update query to copy/convert the data to appropriate types in the target table.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•