Find unmatched query with selected date

  • Thread starter Thread starter fox
  • Start date Start date
F

fox

Hello,
I get a question. I have a query to find unmatched data to print a
report. Now I need to add the selected date range to find the unmatched date,
but the date is inside the other table(for comparing). What should I do in
this case?
Table A(ID, Name, xxxxxxxx)
Table B(ID, Date, xxxxxx)

Find table A unmatch data. The unmatch wizzard give me Table B.Id is "is
Null". Now I would like to add Date range, like 1/1/09 to 1/31/09.

Thank you very much.

Fox
 
Open your unmatch query in design view. Click on your date field in table B
and drag to a blank in the Field row of the grid if it is not there already.
Then in the Criteria row below your date field of table B enter this --
Between #1/1/09# AND #1/31/09#

Beaware that if your date field also includes time then those dated 1/31/09
will not be pulled as a date with time is greater than a date without time
due to time being a decimal fraction added to the date number. So you would
need to use the following date instead.
 
If you apply criteria to the records in table B then you no longer will have a
left join.

If you just need the information in table A, then the following will work well
with small datasets.

SELECT [Table A].*
FROM [TABLE A]
WHERE [Table A].ID NOT in
(SELECT ID
FROM [Table B]
WHERE ID is Not NULL
And [Date] Between #1/1/09# and #1/31/09#)

If that is too slow, then post back for an alternative query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top