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.
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
Which are number values (assuming your Excel is set up to use the 1900 date system):
In column C you have dates such as
Which are number values:
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?
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.
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
Cells(i, 2) = Mid(MyString, 1, DSLOC - 1)