Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Date/Time field export issue

    Hi All,

    I have some code in an application that is failing due to a Date Comparison issue. Two of my Access tables have dates that come from different sources. My application makes decisions based on the difference between the dates. Here is the problem... Even when the dates are the same they are not seen as the same date by my application.

    I exported the fields to Excel and used the Match() function. Even though the dates look the same, they do not match. I would really appreciate any assistance on this.

    The field in both tables in my db are set to Date/Time with a Short Date Format. When I look at the fields in excel they both are formatted a date format.


    I have attached a file so you can see the issue. Notice if you type the date in over the existing value in Column C they will match. So there is something wrong with my dates in column C.

    Compare Dates.zip

    Any Ideas? Even if I have to dump it to Excel, manipulate it, and then bring the data back in I am fine. Just need a fast solution...

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Brent,

    In Excel worksheets dates are treated as double data types. Formatting a cell as a date or as a number does not change the underlying value - it just changes it's appearance.

    A day (24 hours) has a value of 1.
    So 12 hours has a value of 0.5.




    In column B you have dates such as
    26/05/2009
    13/05/2009

    Which are number values (assuming your Excel is set up to use the 1900 date system):
    39959
    39946



    In column C you have dates such as
    26/05/2009 12:00
    13/05/2009 12:00

    Which are number values:
    39959.5
    39946.5


    I know that's not what shows in the cell, but that because of the way you've formatted it. If you select a cell in column C and look in the formula bar you will see the time stamp. Or, if you change the formatting to a number you will see the .5.




    So that is why they don't match each other.

    The resolution? No need to export to Excel to make such a simple manipulation. Either append or truncate the time portion as necessary in Access?


    Hope that helps...

  3. #3
    Join Date
    Jun 2007
    Posts
    74

    Thumbs up

    Colin,

    Thanks for the response. I actually found that last night. I am doing a bit of my pre-processing using vba in excel. My source data comes from several internal websites. Unfortunately the owners of the applications I need data from are not willing to give anyone direct access to the data without God's approval. So while I am waiting on that I have to use data I pull form their websites. Since all of the data comes from different sources, I am stuck with getting the data formatted prior to getting it into my db. I have added the following code to my pre-processing macro. This seems to fix the issue. Exactly as you suggested, the fix is to strip the additional information out of the date string.


    Code:
    Sub DateFix()
    Dim i As Long, DSLOC As Integer, lastrow As Long
    Dim MyString As String
    lastrow = ActiveSheet.UsedRange.Rows.Count
    
    For i = 2 To lastrow
    
        MyString = Cells(i, 2)
        For DSLOC = 1 To Len(MyString)
            If Mid(MyString, DSLOC, 1) = " " Then
                Pos = DSLOC
                Exit For
            End If
        Next DSLOC
        
        Cells(i, 2) = Mid(MyString, 1, DSLOC - 1)
        
    Next i
    End Sub
    Thanks again,
    Brent

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Brent,

    If this becomes a regular chore you could have a look at the Text To Columns utility which will chop of the time stamp in a flash compared to a VBA loop.

    Glad you got it sorted...

Posting Permissions

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