Query not return all records

[FJB]

I have a select query that has begun to malfunction. Its purpose is to
identify those records who have expiration dates three months from the
current system month, i.e. today we would see those which will expire
in March 2006. The expiration date is calculated in the query from the
last date. Currently the query returns four records and should return
12 When looking at the ID numbers, it appears that the later records
are not being included (ID#'s: 32, 53, 90, 93, 111, 120, 157, 171,
249, 254, 287, 321). ID's 32, 53, 90, and 111 are the current result.

Below is the query in SQL view:

SELECT [Foreign Bank Certification Come-up].[Foreign Bank], [Foreign
Bank Certification Come-up].[Last Date], [Foreign Bank Certification
Come-up].[Group], Format(DateAdd("yyyy",3,[Last Date]),"yyyymm") AS
ExpirationMonth
FROM [Foreign Bank Certification Come-up]
WHERE (((Format(DateAdd("yyyy",3,[Last
Date]),"yyyymm"))=Format(DateAdd("m",3,Date()),"yyyymm")) AND
(([Foreign Bank Certification Come-up].Closed)="No"))
ORDER BY [Foreign Bank Certification Come-up].[Last Date];

Thanks, as always, for your help

Frank

 

[John Spencer]

I might try the following.

SELECT [FB].[Foreign Bank], [FB].[Last Date], [FB].[Group]
, Format(DateAdd("yyyy",3,[Last Date]),"yyyymm") AS ExpirationMonth
FROM [Foreign Bank Certification Come-up] as FB
WHERE [Last Date] Between DateSerial(Year(Date())-3, Month(Date())+3,1)
And DateSerial(Year(Date())-3,Month(Date())+4,0)
AND [FB].Closed)="No"
ORDER BY [FB].[Last Date];

If this doesn't work, you might try hard-coding the dates and see what
values are returned
Where [Last Date] Between #3/1/2003# and #3/31/2003#