select upcoming month, but selects every year

[pb]

I'm using the following to find out who is expected to by recalled
(RECALLDUEDATE;shortdate format) by upcoming month. 2 Tables. PATIENTS &
SCHEDULED. PT_ID is unique and is the PK of PATIENTS Table.
The problem is if 2 patients have a RECALLDUEDATE of January 1st, 2006 and
January 10th, 2007, both of them are selected for this upcoming january. How
can i modify below to select only by upcoming month, without selecting all
the years?

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
MONTH(SCHEDULED.RECALLDUEDATE)=1

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
MONTH(SCHEDULED.RECALLDUEDATE)=2
..
..
..
..down to december...
..
..
SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
MONTH(SCHEDULED.RECALLDUEDATE)=12

 

[Wayne Morgan]

If you are just wanting RecallDueDates for the next month following the
month of the current date then:

WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE Between
DateSerial(Year(Date()), Month(Date()) + 1, 1) And DateSerial(Year(Date()),
Month(Date()) + 2, 0)

The zero in the last part is really 1 - 1. You are going to the first day of
the 2nd month ahead and subtracting one day. With this, you won't have to
specify the month, it will automatically look at the month following the
month of the current date.

 

[pb]

thanks.....how would i modify this if i needed, lets say, this coming up
januarys RECALLDUEDATE?, and febs, march's...etc.

 

[Wayne Morgan]

If you know the month that you want, you could just put in the first and
last dates of the month instead of using the DateSerial function.

WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE Between
#1/1/2006# And #1/31/2006#

The dates need to be in the US format of month/day/year. There are other
ways to manipulate this, depending on what you're trying to do. You could
make the dates parameters which would cause the query to prompt you for the
dates when it opens.

Example:
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE Between
DateSerial([Enter the year], Month([Enter the month], 1) And
DateSerial([Enter the year], [Enter the month] + 1, 0)

This would prompt you for the year and month that you are looking for. Enter
the values as numbers (i.e. 2006 for the year and 1 for January). It will
automatically compute the first and last day of the month you entered and
give you the records within those dates.