Using criteria to calculate last 4 months

[rcollins]

I have a query that runs off a criteria form. What I need to do is fix it so that when I select June from the combo box, it gives me back June, May, April and March. The format in the combo box (Combo6) is mmm 'yy so I get Jun '09. The field that I am using in the query is the "Format SurveyDate as Expr 1" and my form is named Criteria. Please advise me on how I need to do this. Thanks in advance

SELECT tStaffData_1.staffname AS Auditor, tStaffData.staffname AS Staff, q_Scoring_P1.QuestionCounter, q_Scoring_P1.NA, q_Scoring_P1.Score, Round(nz([score])/([questionCounter]-[NA])*100,2) AS Grade, (Format([SurveyDate],"mmm"" '""yy")) AS Expr1, tSurvey.SurveyName, tAudit.PatientId, tPatientData.PatientName, tStaffData.clinic_id, tStaffData.service_id, Format([SurveyDate],"yyyymm") AS Expr2
FROM tPatientData RIGHT JOIN ((tStaffData INNER JOIN (tStaffData AS tStaffData_1 INNER JOIN (tAudit INNER JOIN q_Scoring_P1 ON tAudit.AuditID = q_Scoring_P1.AuditID) ON tStaffData_1.staff_id = tAudit.AuditorId) ON tStaffData.staff_id = tAudit.StaffID) INNER JOIN tSurvey ON tAudit.SurveyId = tSurvey.SurveyId) ON tPatientData.patient_id = tAudit.PatientId
WHERE (((tSurvey.SurveyName)="Quality Review Tool"))
ORDER BY Format([SurveyDate],"yyyymm");
 

[ADezii]

@rcollins
How about using the BETWEEN Operator as indicated in Line #10, something similar to:

SELECT tStaffData_1.staffname AS Auditor, tStaffData.staffname AS Staff, q_Scoring_P1.QuestionCounter, _
q_Scoring_P1.NA, q_Scoring_P1.Score, Round(nz([score])/([questionCounter]-[NA])*100,2) AS Grade, _
Format([SurveyDate],"mmm"" '""yy") AS Expr1, tSurvey.SurveyName, tAudit.PatientId, _
tPatientData.PatientName, tStaffData.clinic_id, tStaffData.service_id, Format([SurveyDate],"yyyymm") AS Expr2
FROM tPatientData RIGHT JOIN tStaffData INNER JOIN tStaffData AS tStaffData_1 INNER JOIN _
tAudit INNER JOIN q_Scoring_P1 ON tAudit.AuditID = q_Scoring_P1.AuditID ON _
tStaffData_1.staff_id = tAudit.AuditorId ON tStaffData.staff_id = tAudit.StaffID INNER JOIN _
tSurvey ON tAudit.SurveyId = tSurvey.SurveyId ON tPatientData.patient_id = tAudit.PatientId
WHERE tSurvey.SurveyName = "Quality Review Tool And [SurveyDate] _
Between DateAdd('m',-3,Forms!<Form_Name>!<Control_Name>) And Forms!<Form_Name>!<Control_Name>
ORDER BY Format([SurveyDate],"yyyymm");

[rcollins]

Here is My error-"The expression is typed incorrectly, or it is too complex to be evaluated." Here is what I have

WHERE ((((tSurvey.SurveyName)="Quality Review Tool")) AND ([SurveyDate] Between DateAdd('m',-3,[Forms]![Criteria]![Combo6]) And [Forms]![Criteria]![Combo6]))

Does it make a difference with this as my format?

Expr1: (Format([SurveyDate],"mmm"" '""yy"))

[ADezii]

@rcollins
Try:

Expr1:Format([SurveyDate],"mmm yy") 

[rcollins]

Perfect!!! Thank you so much. I would have never guessed it was in the way I was formatting the date

[ADezii]

@rcollins
You are quite welcome, rcollins.