Using criteria to calculate last 4 months  | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| |
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");
-
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Using criteria to calculate last 4 months
Quote:
Originally Posted by 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");
-
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");
| | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| | | re: Using criteria to calculate last 4 months
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"))
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Using criteria to calculate last 4 months
Quote:
Originally Posted by 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"))
Try: - Expr1:Format([SurveyDate],"mmm yy")
| | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| | | re: Using criteria to calculate last 4 months
Perfect!!! Thank you so much. I would have never guessed it was in the way I was formatting the date
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Using criteria to calculate last 4 months
Quote:
Originally Posted by rcollins Perfect!!! Thank you so much. I would have never guessed it was in the way I was formatting the date You are quite welcome, rcollins.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
