Results 1 to 10 of 10

20081001, 10:24 #1
 Join Date
 Jan 2002
 Location
 Hunt Valley, Maryland, USA
 Posts
 88
 Thanks
 58
 Thanked 0 Times in 0 Posts
Date convert  string to m/dd/yy (Access 2003)
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 1012 contain six digits, but the other "dates" contain five digits.
Is there either a VBA or a nonVBA 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.

20081001, 10:38 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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.

20081001, 10:55 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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.

20081001, 11:06 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Date convert  string to m/dd/yy (Access 2003)
<P ID="edit" class=small>(Edited by Jezza on 01Oct08 23:06. To change the code to take into consideration different string lengths)</P>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])
HTHJerry

20081002, 02:57 #5
 Join Date
 Jan 2002
 Location
 Hunt Valley, Maryland, USA
 Posts
 88
 Thanks
 58
 Thanked 0 Times in 0 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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 <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

20081002, 03:02 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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.

20081002, 05:18 #7
 Join Date
 Jan 2002
 Location
 Hunt Valley, Maryland, USA
 Posts
 88
 Thanks
 58
 Thanked 0 Times in 0 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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

20081002, 05:22 #8
 Join Date
 Jan 2002
 Location
 Hunt Valley, Maryland, USA
 Posts
 88
 Thanks
 58
 Thanked 0 Times in 0 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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 5character and 6character strings. You supplied the meat of the solution. Cheers.

20081002, 05:29 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
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 +.

20081002, 08:19 #10
 Join Date
 Jan 2002
 Location
 Hunt Valley, Maryland, USA
 Posts
 88
 Thanks
 58
 Thanked 0 Times in 0 Posts
Re: Date convert  string to m/dd/yy (Access 2003)
Impresssive, as usual. Many thanks!