Query/Function/Parameter Troubles...

R

RobertG

Hi All,

I am having a little bit of trouble with a query of mine relating to
training courses. 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.

Here is the 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 PeriodFROM (tblFrequency RIGHT JOIN
tblTrainingType ON tblFrequency.FrequencyID = tblTrainingType.Frequency)
RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON tblStaff.StaffID =
tblTraining.StaffID) ON tblTrainingType.TrainingTypeID =
tblTraining.TrainingTypeWHERE (((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
--------------------------------------------------------------------------------

As always, feel free to ask for any more details and I will be happy to
supply. Any ideas/suggestions would be greatly appreciated...

Thanks for taking a look at this,

Robert
 
K

KARL DEWEY

when I try to specify one, the DueDate paremeter box comes up.
DueDate is a calculated field. You then use that alias in another
calculation. The problem is that Access is seeing the call for the alias
before it is first calculated.
Use your calculation that derives DueDate instead of the alias.

You also seem to have some information run together or maybe just the post --
AS PeriodFROM
tblTraining.TrainingTypeWHERE

--
KARL DEWEY
Build a little - Test a little


RobertG said:
Hi All,

I am having a little bit of trouble with a query of mine relating to
training courses. 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.

Here is the 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 PeriodFROM (tblFrequency RIGHT JOIN
tblTrainingType ON tblFrequency.FrequencyID = tblTrainingType.Frequency)
RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON tblStaff.StaffID =
tblTraining.StaffID) ON tblTrainingType.TrainingTypeID =
tblTraining.TrainingTypeWHERE (((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;
--------------------------------------------------------------------------------

As always, feel free to ask for any more details and I will be happy to
supply. Any ideas/suggestions would be greatly appreciated...

Thanks for taking a look at this,

Robert
Click to expand...
 
R

RobertG

Hi Karl,

Regarding the SQL running together, it must have just been the post... it
looks good on my end.

As for your solution... it worked, perfectly! Thank you very much!

Robert

KARL DEWEY said:
DueDate is a calculated field. You then use that alias in another
calculation. The problem is that Access is seeing the call for the alias
before it is first calculated.
Use your calculation that derives DueDate instead of the alias.

You also seem to have some information run together or maybe just the post --
AS PeriodFROM
tblTraining.TrainingTypeWHERE

--
KARL DEWEY
Build a little - Test a little


RobertG said:
Hi All,

I am having a little bit of trouble with a query of mine relating to
training courses. 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.

Here is the 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 PeriodFROM (tblFrequency RIGHT JOIN
tblTrainingType ON tblFrequency.FrequencyID = tblTrainingType.Frequency)
RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON tblStaff.StaffID =
tblTraining.StaffID) ON tblTrainingType.TrainingTypeID =
tblTraining.TrainingTypeWHERE (((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;
--------------------------------------------------------------------------------

As always, feel free to ask for any more details and I will be happy to
supply. Any ideas/suggestions would be greatly appreciated...

Thanks for taking a look at this,

Robert
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid use of null 3
Update query stopped working 1
Multiple supervisors over a date range in query 1
update query 4
Delete query date field pulling more than one month 10
Select query driving me mad (using *) 4

Top