By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Using criteria to calculate last 4 months

rcollins
100+
P: 234
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE (((tSurvey.SurveyName)="Quality Review Tool"))
  4. ORDER BY Format([SurveyDate],"yyyymm");
  5.  
Jul 13 '09 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,679
@rcollins
How about using the BETWEEN Operator as indicated in Line #10, something similar to:
Expand|Select|Wrap|Line Numbers
  1. SELECT tStaffData_1.staffname AS Auditor, tStaffData.staffname AS Staff, q_Scoring_P1.QuestionCounter, _
  2. q_Scoring_P1.NA, q_Scoring_P1.Score, Round(nz([score])/([questionCounter]-[NA])*100,2) AS Grade, _
  3. Format([SurveyDate],"mmm"" '""yy") AS Expr1, tSurvey.SurveyName, tAudit.PatientId, _
  4. tPatientData.PatientName, tStaffData.clinic_id, tStaffData.service_id, Format([SurveyDate],"yyyymm") AS Expr2
  5. FROM tPatientData RIGHT JOIN tStaffData INNER JOIN tStaffData AS tStaffData_1 INNER JOIN _
  6. tAudit INNER JOIN q_Scoring_P1 ON tAudit.AuditID = q_Scoring_P1.AuditID ON _
  7. tStaffData_1.staff_id = tAudit.AuditorId ON tStaffData.staff_id = tAudit.StaffID INNER JOIN _
  8. tSurvey ON tAudit.SurveyId = tSurvey.SurveyId ON tPatientData.patient_id = tAudit.PatientId
  9. WHERE tSurvey.SurveyName = "Quality Review Tool And [SurveyDate] _
  10. Between DateAdd('m',-3,Forms!<Form_Name>!<Control_Name>) And Forms!<Form_Name>!<Control_Name>
  11. ORDER BY Format([SurveyDate],"yyyymm");
Jul 13 '09 #2

rcollins
100+
P: 234
Here is My error-"The expression is typed incorrectly, or it is too complex to be evaluated." Here is what I have
Expand|Select|Wrap|Line Numbers
  1. 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?
Expand|Select|Wrap|Line Numbers
  1. Expr1: (Format([SurveyDate],"mmm"" '""yy"))
Jul 14 '09 #3

ADezii
Expert 5K+
P: 8,679
@rcollins
Try:
Expand|Select|Wrap|Line Numbers
  1. Expr1:Format([SurveyDate],"mmm yy") 
Jul 14 '09 #4

rcollins
100+
P: 234
Perfect!!! Thank you so much. I would have never guessed it was in the way I was formatting the date
Jul 14 '09 #5

ADezii
Expert 5K+
P: 8,679
@rcollins
You are quite welcome, rcollins.
Jul 14 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.