Date/Time field export issue

Brent Blevins · #1 (**permalink**) 05-12-09, 21:37

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...

Colin Legg · #2 (**permalink**) 05-13-09, 15:39

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...

Brent Blevins · #3 (**permalink**) 05-14-09, 15:47

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

Colin Legg · #4 (**permalink**) 05-14-09, 16:06

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech