Convert Date

[renenger]

I have a query in which I am splitting a long integer into Month, Day, Year and then putting it back together using Date Serial in another query. When I originally set this up, the date was coming through as 5 numbers. Now it is coming through as six. However, Access will error out if I try to set it as a date.

So, how can I redo the fields below to get 2 digits for the month instead of one. The date now comes through as 041706

CInt(Mid([Field1],1,2)) AS iMo,
CInt(Mid([Field1],2,2)) AS iDay,
CInt(Mid([Field1],4,2)) AS iYear

 

[neileg]

If you have 6 digit dates with a leading zero, they can't be numeric as the leading zero would be dropped. They must be text. The following formula will convert a six character text date to a real date:
CDate(Left([TxtDate],2) & "/" & Mid([TxtDate],3,2) & "/" & Right([TxtDate],2))

If they are numeric, and you have a mixture of 5 and 6 digit dates, you can test the length with Len() and append a leading "0" if it is 5 digits and "" if it is 6. This will convert the numeric date to a text date and you can apply the above formula to that.