Query Criteria to Show Unmatched If Date RANGE is Null

[RMS123]

Hi there,

I am pretty new with Access and don't know how to go about this.

I am trying to show unmatched entries (from 2 Queries) if it is not within a date before or after the entry based in the other AND if trace number doesn't match. I hope that all makes sense....

Here is what I have for SQL code, hope someone could help me out .

SELECT [Posted Not Remitted].TRACE, [Posted Not Remitted].ACCOUNT, [Posted Not Remitted].CARD, [Posted Not Remitted].AMOUNT, [Posted Not Remitted].Date, [Posted Not Remitted].Error
FROM [Posted Not Remitted] LEFT JOIN [Remitted Not Posted] ON ([Posted Not Remitted].Date = [Remitted Not Posted].Date) AND ([Posted Not Remitted].[TRACE] = [Remitted Not Posted].[TRACE])
WHERE ((([Remitted Not Posted]![Date]-1) Is Null) AND (([Remitted Not Posted]![Date]+1) Is Null) AND (([Remitted Not Posted].Date) Is Null) AND (([Remitted Not Posted].TRACE) Is Null));

 

[xenou]

Why are you using a bang operator (!) here:

[Remitted Not Posted]![Date]

Is this in a form?

Otherwise, since you have Date = Date in the On clause you will only get matches on exact dates, not ranges of dates, and I don't see how the use of Null checks is helping to create a range either. If you put equals in the ON clause than that's going to override whatever you do in the WHERE clause.

For a range of dates you need something like:
where [Date] > X and [Date] < Y

or:
where [Date] between (X and Y)

 

[RMS123]

So simple! Thanks so much

 

[xenou]

Good - glad it's working!