I am having a little bit of trouble with a query of mine relating to training. This query is supposed to look at the last date a person took a particular training course and calculate a "DueDate" based on that. From there, I would like to see what quarter and year, "Period" (QYYYY format), the DueDate falls in, so my user can specify which Period to view.
So far, the query gives me the correct DueDate and Period, but I am having trouble using any criteria with the Period... when I do, a parameter input box is displayed asking for DueDate. Ideally, I would like to use a function, fPeriod(), that would be a Long value which changes depending on user input through a form, as the criteria for this query.
Again, the query works fine without any criteria in Period, however, when I try to specify one, the DueDate paremeter box comes up.
Attached is a screen shot of the pertinant fields, and here is the full SQL:
CODE
SELECT tblTraining.StaffID, tblStaff.LName & ", " & tblStaff.FName AS StaffName, tblTrainingType.TrainingType, tblTraining.TrainingType, tblFrequency.Frequency, tblStaff.AnniversaryDate, tblTraining.TrainingDate, IIf([trainingdate] Is Null,Null,IIf([frequencyid]=2,DateSerial(Year([trainingDate])+1,Month([anniversarydate]),Day([anniversarydate])),DateSerial(Year([trainingDate])+3,12,31))) AS DueDate, Format([DueDate],"qyyyy") AS Period
FROM (tblFrequency RIGHT JOIN tblTrainingType ON tblFrequency.FrequencyID = tblTrainingType.Frequency) RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON tblStaff.StaffID = tblTraining.StaffID) ON tblTrainingType.TrainingTypeID = tblTraining.TrainingType
WHERE (((IIf([trainingdate] Is Null,Null,IIf([frequencyid]=2,DateSerial(Year([trainingDate])+1,Month([anniversarydate]),Day([anniversarydate])),DateSerial(Year([trainingDate])+3,12,31))))>=Date()) AND ((tblFrequency.FrequencyID)=2)) OR (((IIf([trainingdate] Is Null,Null,IIf([frequencyid]=2,DateSerial(Year([trainingDate])+1,Month([anniversarydate]),Day([anniversarydate])),DateSerial(Year([trainingDate])+3,12,31))))>=Date()) AND ((tblFrequency.FrequencyID)=3))
ORDER BY tblTraining.StaffID;
FROM (tblFrequency RIGHT JOIN tblTrainingType ON tblFrequency.FrequencyID = tblTrainingType.Frequency) RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON tblStaff.StaffID = tblTraining.StaffID) ON tblTrainingType.TrainingTypeID = tblTraining.TrainingType
WHERE (((IIf([trainingdate] Is Null,Null,IIf([frequencyid]=2,DateSerial(Year([trainingDate])+1,Month([anniversarydate]),Day([anniversarydate])),DateSerial(Year([trainingDate])+3,12,31))))>=Date()) AND ((tblFrequency.FrequencyID)=2)) OR (((IIf([trainingdate] Is Null,Null,IIf([frequencyid]=2,DateSerial(Year([trainingDate])+1,Month([anniversarydate]),Day([anniversarydate])),DateSerial(Year([trainingDate])+3,12,31))))>=Date()) AND ((tblFrequency.FrequencyID)=3))
ORDER BY tblTraining.StaffID;
Thanks for taking a look at this,
Robert