Results 1 to 10 of 10
  1. #1
    Star Lounger
    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 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.

  2. #2
    Plutonium Lounger
    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.

  3. #3
    Plutonium Lounger
    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.

  4. #4
    Platinum Lounger
    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 01-Oct-08 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])

    HTH
    Jerry

  5. #5
    Star Lounger
    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>

  6. #6
    Plutonium Lounger
    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.

  7. #7
    Star Lounger
    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

  8. #8
    Star Lounger
    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 5-character and 6-character strings. You supplied the meat of the solution. Cheers.

  9. #9
    Plutonium Lounger
    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 +.

  10. #10
    Star Lounger
    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!

Posting Permissions

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