Share via

Query Join on dates that don't match (but do)

Anonymous
2011-04-30T01:04:26+00:00

I have a large table (over 1 million records) and a small table (over 1,000 records) that have a date field in common(Date plus time, ie., 9/20/2008 12: 54 PM) ,or so I thought. I made a query that joins on dates but only a few match records result when nearly all dates in the large table should be in the small able.  I used a one-way join on the large table (and unique records) and show matching and unmatched dates. The unmatched dates (and times) indeed appear in the small table but Access disagrees. I mean the dates from the two tables lok identical in every respect. I have subtracted the two unmatched dates in Excel to get a zero result.  I have looked at serial date to 7 decimals and those match(n Excel). I can export to Excel or to a delimited text file (Excel does not like 1.5 million records) and re-import to Access and those dates then match the dates in the small table (most of the time, hard to say for sure with over a million records).   The export/import routine is a pain and it worries me that some records still apparently do not match. I would like to use an Access update query to 'fix' all the dates in the large table (replace) with a matching date such that the join would work properly. Has anyone experienced a is-math of a match like this before. Is this a version problem perhaps (am using Office 10 on Access 2003 format files) Any comments appreciated.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-04-30T18:03:38+00:00

Nevertheless, as John has said, the values of the underlying floating point number may not be the same at their full precision, which can be to less than one second.  Do as John says and update the values to the value of the return value of the Format function, though I think what he actually meant was:

CDate(Format([datefield], "yyyy-mm-dd hh:nn:ss"))

as the DateValue function returns an integer value, i.e. one which represents a date with a zero time of day, midnight at the start of the day.  The Format function returns a string expression, which is of course absolute for each same value, so the resulting floating point numbers by which date/time values are implemented should, after calling the CDate function, be exactly the same for any given formatted date/time regardless of any unseen discrepancies in the original values.  The format which John has used here BTW corresponds to the ISO standard for date/time notation, so is internationally unambiguous.

Even without updating the values, you could of course join the tables on:

Format([SmallTable].[DateTimeColumn],"yyyymmddhhnnss") = Format([BigTable].[DateTimeColumn],"yyyymmddhhnnss")

That should guarantee matches regardless of any unseen discrepancies in the underlying values, but performance probably won't be great.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2011-04-30T23:18:00+00:00

My deep apologies!!!! My mind was wandering; DateValue in fact trims off the time portion. CDate() is the appropriate function, as Ken said.

Sure hope you had a backup. I'd offer to enter the data manually but...

:-{(

Was this answer helpful?

0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-05-09T04:53:55+00:00

    Yes, I had a backup but actually tested on a small test table.  Interesting that the format sting converts the values to text (I think) thus get a type-miss-match on my join the to large table until the DateValue or CDate expression was included. As you say, Datevalue just trimmed all times and the the format sting gave date and 00:00:00. Cdate worked fine once I found the matching format to the dates in the large table.  I also found the discussion about the international formats being more portable but that wqas not an issue with my project. Thanks for your time.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-04-30T02:39:22+00:00

    This is pure speculation, but... it's possible that the dates are different at a sub-second (or even sub-millisecond) level. They're stored as Double Floats, and you could be just getting roundoff error.

    One idea does come to mind: make a backup, of course, and then update the datefield in each table to

    DateValue(Format([datefield], "yyyy-mm-dd hh:nn:ss"))

    This will truncate the datefield to the nearest second (the best precision you can usefully handle) and convert that back to a - hopefully consistant! - date/time value. If you only care about the value to the minute, use "yyyy-mm-dd hh:nn" instead (truncating the seconds).

    Was this answer helpful?

    0 comments
  3. Tom van Stiphout 40,211 Reputation points MVP Volunteer Moderator
    2011-04-30T02:22:33+00:00

    Take a look at the database design. Do both date fields have a data type of "Date/Time"?

    Was this answer helpful?

    0 comments