Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2011
    Posts
    12

    Unanswered: Access module to convert date

    Problem: Access table was provided to me with the date fields as integer format (20040625). There are 20 tables with various different date fields in each one.
    I need to run various queries that will convert the date into date format (06/25/2004).
    I have developed the following formula to do this (the field name was [HireDate]

    DateSerial((Mid([HireDate],1,4)),(Mid([HireDate],5,2)),(Mid([HireDate],7,2)))

    This returns the correct result and so I know that the query works.

    What I need.
    I need a module that will enable me to access the formula from within the expression builder and apply the same formula to the various different date fields within the different tables that I may require

    Example
    table name T1
    Date field name D1

    Your assistance is much appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You don't actually NEED a module to convert the date. Just add a field to the query (a calculated field) where the new name of the field is different that HireDate then add a colon followed by your formula. For example

    DateHired: DateSerial((Mid([HireDate],1,4)),(Mid([HireDate],5,2)),(Mid([HireDate],7,2)))

    That will add a DateHired field to the querythat is now a date data type.

    If you still want a module then that is pretty easy as well.

  3. #3
    Join Date
    Jan 2011
    Posts
    12
    Thanks for the reply.
    yes i was aware of that, however, i thought it may be quicker to be able to access the module from within the expression builder than to have to change the name of the field. as there are around 20 different field names. and in some cases i am drawing on 3 tables at a time.
    so if you could assist with the module would be much appreciated.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think I understand what you're trying to do. Something like this?

    Code:
    Public Function ConvertDate(lngInput As Long) As Date
      ConvertDate = DateSerial((Mid(lngInput,1,4)),(Mid(lngInput,5,2)),(Mid(lngInput,7,2)))
    End Function
    Paul

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    PBaldy beat me to it, but here is my function:

    Code:
    Public Function ConvertDate(varDate As Variant) As Date
    
        Dim strDate As String
        
        strDate = Nz(varDate, "")
        
        ConvertDate = 0
        
        If strDate <> "" Then ConvertDate = DateSerial((Mid(strDate, 1, 4)), (Mid(strDate, 5, 2)), (Mid(strDate, 7, 2)))
        'If strDate <> "" Then ConvertDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
        
    End Function
    Mine assumes that not every date field has a value. If the date field is Null then the function won't work. The only way to pass in Null values is to a Variant data type.

    I also added, for comparison, a line that I commented out, what i typically do when I am converting dates in the format you have them. I don't know if either one is faster than the other one.
    Last edited by DCKunkle; 01-06-11 at 15:58.

  6. #6
    Join Date
    Jan 2011
    Posts
    12
    Thanks DCKnuckle & Pbaldy

    the following works faster
    'If strDate <> "" Then ConvertDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))

    with all 3 functions - 2 from DCknuckle and 1 Pbaldy i get a run time error 13. Type mismatch.

    The error comes from one of the cells which has a zero instead of a 8 digit number.

    is there a way to incorporate that into the equation and perhaps output a message "missing date".
    thanks

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    DCKunkle's solution was more polished; mine was intended be a conceptual direction. With any method, you need to account for anything the function might encounter. It sounds like you also need to test for zeros. Based on your last, I'd do it along these lines:

    Code:
    Public Function ConvertDate(varInput As Variant) As String
    
      If IsNull(varInput) OR varInput = 0 Then
        ConvertDate = "Missing date"
        Exit Function
      End If
    
      ConvertDate = DateSerial((Mid(varInput,1,4)),(Mid(varInput,5,2)),(Mid(varInput,7,2)))
    End Function
    Last edited by pbaldy; 01-06-11 at 17:27. Reason: fix data type goof
    Paul

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would probably do the following:

    Code:
    Public Function ConvertDate(varDate As Variant) As Date
    
        Dim strDate As String
        
        strDate = Nz(varDate, "0")
        
        ConvertDate = 0
        
        If strDate <> "0" Then ConvertDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
        
    End Function
    It will treat all nulls and 0 the same way.

  9. #9
    Join Date
    Jan 2011
    Posts
    12

    Solved

    thanks to both of you & the internet & my VB & VBA in a nutshell book

    today has been a trying day for a VBA beginer as myself.
    however, i now understand a bit more.

    Both methods seem to work equally well and no errors coming up yet.

    thanks again

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo. This is one of those situations where there are several ways to accomplish the same goal. DCKunkle's method is equally good, but returns a zero date rather than the text you mentioned in post 6 when the input is Null or zero. Either is fine, just depends on what you want to see.
    Paul

  11. #11
    Join Date
    Jan 2011
    Posts
    12

    UPDATE - sorting data problem - solved

    As it turns our for my formula results i liked that i coudl see "missing data" however, when i went to calculate the days b/t 2 dates (where 1 was the text "missing data") i would get an error - which was ok as i could then look to see why the error.
    however, when i went to sort or select days greater or less than a range i got a data type mismatch error.
    i suspect that this is b/c of the text in the calculated field.

    i have chosen to go with DCknuckles formula b/c it seems to run faster and if there is a zero i can usually see the erro in the days between dates b/c of the extra large variance.

    I am learning that there are more ways to solve the same problem. some may just work better under certain conditions.

Posting Permissions

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