They changed the request overnight !!
Below is what I am trying to do now,
I have the list of patients who were in care during the years 2004, 2005 and 2006.
I want to find the patients who were 18 years and 1 month old in Jan 2004, Feb 2004.....Dec 2004 (basically each month of the year). Similarly I need to do the same for 2005 and 2006.
Please treat this as urgent....thanks for the help.
Make a Table for the Dates (Month end dates) in question (1/31/2004 thru 12/31/2006). This will make it easy for you to maintain vice dynamically creating an array and looping thru it. Just 1 column needed. Name the column MoEnd (month and year are reserverd words - can be used but good practice not to). Name the table - tblDates. You can name it all whatever you want but you will need to change what I post here. (Best to leave out spaces when naming as well use underscores instead).
If you are looking for patients between 17 yrs and 1 month and 18 yrs and 1 month (205 months to 217 months):
Select Format(MoEnd,"mm_yyyy") as Month_Yr, YourAdmitTable.CaseID,YourAdmitTable,SeqNbr, 'Y' As [InRange]
From YourAdmitTable, MoEnd
Where DateDiff("m",Birthdate,MoEnd) Between 205 and 217
Order By MoEnd;
If looking for 217 month olds change where line to:
Where DateDiff("m",Birthdate,MoEnd) = 217
If looking for all less than 217 month olds change where line to:
Where DateDiff("m",Birthdate,MoEnd) < 217
This will get you going since we are not really sure on EXACTLY what you are looking for.
Have a great weekend!