Matching/join using date +/- tolerance of several days

[szhjcn]

We need to match the data from 2 different tables, but sometimes the date is
not exactly the same in both tables (can differ by upto several days).

Searched the web and here but could not find an answer, but I'm sure there
must be one?

The 'main' join will be done based on the 'account', then need to check
values dates, currency etc..

 

[Douglas J. Steele]

Try something along the lines of:

WHERE Table1.Field1 = Table2.Field1
AND Table1.Field2 = Table2.Field2
AND DateDiff("d", Table1.DateField, Table2.DateField) < 2

Experiment with the "2": that will return records that have the same values
for fields Field1 and Field2, and have dates within 2 days of one another.
Note that you may well get more rows back than you anticipate, since you may
have matches you weren't anticipating.