Date convert – string to m/dd/yy (Access 2003)

Topic

New Reply

For a client, I created a db having one table, which contains over 1.3M records and 32 fields. There are three date fields. In my haste to import the source text file, I set up only one of the three date fields as dates — the other two came in as long integers.
With long integers, of course, a date intended to be “5/12/06” becomes “51206”, not “051206”. Thus, “dates” in months 10-12 contain six digits, but the other “dates” contain five digits.
Is there either a VBA or a non-VBA solution to convert all those 1.3M x 2 = 2.6M long integers in those two fields to dates?
I failed pretty badly trying to come up with an update query to do this. By the way, I would be happy to do just one of the two date fields at a time, if that simplifies the process!
Thanks very much.

Reply | Quote

Replies

Let’s say that the two number fields are named Long1 and Long2
Create two new date/time fields in the table, let’s say Date1 and Date2.

Create a query based on the table.
Add Long1 and Date1 to the query grid.
Select Query | Update Query.
Set the Criteria for Long1 to Is Not Null.
Set the Update to for Date1 to

DateSerial(1900 – 100 * (([Long1] Mod 100) < 30) + ([Long1] Mod 100), [Long1] 10000, ([Long1] 100) Mod 100)

Run the query.
Do the same for Long2 and Date2.

Reply | Quote

Hans: That works great. I wish I understood more about the way you used backslashes in the formula instead of a slashes. Regardless, thank you for your latest rescue.
John

Reply | Quote

The operator performs integer division: for example 13 5 returns 2, because 5 goes 2 times into 13. The remainder of 3 is discarded.
With a number representing a date such as 51208: 51208 100 returns 512, and 512 Mod 100 is the remainder of 512 after division by 100, i.e. the day number 12.

Reply | Quote

Hans: Please ignore my post #735,972 — I found the necessary reference to usage of the backslash as a truncate function. Also, I have for the first time discovered that the DateSerial function will accept parameters which are not only integers, but real numbers. I tried something like DateSerial(2008.123,4,12), and it dutifully resolved it like DateSerial(2008,4,12)!

Is it possible that the sign after “DateSerial(1900…” in your formula should be plus, not minus? Somehow, the formula seems to resolve to the correct answer with the minus sign, but I don’t understand why.
jes

Reply | Quote

If the year yy is less than 30, for example 08, we assume that it is in this century, i.e. 08 stands for 2008.
If the year yy is 30 or more, for example 95, we assume that it belongs to last century, i.e. 95 stands for 1995.
So we want to add 2000 or 1900 to yy depending on whether it is less than 30 or not.
We do this by adding 1900 in all situations, and then adding another 100 if necessary.

The result of (([Long1] Mod 100) < 30) is either True or False, depending on whether the remainder of Long1 after division by 100 is less than 30 or not.
In VBA and hence in Access, True equals -1 and False equals 0. Multiplying this by 100 we get -100 or 0. To add 100, we subtract -100. Hence the – instead of +.

Reply | Quote

Impresssive, as usual. Many thanks!

Reply | Quote

(Edited by Jezza on 01-Oct-08 23:06. To change the code to take into consideration different string lengths)

In addition you could use the following UDF to convert the date by adding it to a module

Function DateChange(datFormat As String) As Date

Dim datMonth As String
Dim datDay As String
Dim datYear As String

If Len(datFormat) < 6 Then

datMonth = Left(datFormat, 1)
datDay = Mid(datFormat, 2, 2)
datYear = Right(datFormat, 2)

Else

datMonth = Left(datFormat, 2)
datDay = Mid(datFormat, 3, 2)
datYear = Right(datFormat, 2)
End If

DateChange = datMonth & "/" & datDay & "/" & datYear

You can then create a query using the function in the format of NewDate: DateChange([StartDate])

HTH

Reply | Quote

Your code won’t return the correct result if number has 6 digits. This will be the case if the month is 10 or more, e.g. December 5, 2008 would be stored as 120508.

Reply | Quote

Greetings, Jerry:
Thank you for the correction — I had been puzzled for a while… But that was the part of the VBA procedure that I did get right — discriminating between 5-character and 6-character strings. You supplied the meat of the solution. Cheers.

Reply | Quote