Date Criteria Query

[rushilc]

I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.

Thank you!

 

[billmeye]

How to do determine year in your query? Does your query return all sugeries recorded in February regardless of year?

I would suggest using a date range as the criteria for your query and enter an actual date (any day in month) as this is also going to give you the ability to capture the year. Your criteria can be:

Between DateSerial(Year([Forms]![frmYourForm]![TextboxDate]), Month([Forms]![frmYourForm]![TextboxDate]), 1) And DateSerial(Year([Forms]![frmYourForm]![TextboxDate]), Month([Forms]![frmYourForm]![TextboxDate] + 1), 0)

For the 3 month prior query:

Between DateSerial(Year([Forms]![frmYourForm]![TextboxDate]), Month([Forms]![frmYourForm]![TextboxDate]-3), 1) And DateSerial(Year([Forms]![frmYourForm]![TextboxDate]), Month([Forms]![frmYourForm]![TextboxDate] -2), 0)

 

[Brianwarnock]

Monthrequired: iif(parameter>3,parameter-3,parameter+9)

Brian

Bill has given a much more comprehensive answer