UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Query/Function Trouble    
 
   
grazird
post May 15 2008, 04:00 PM
Post #1

UtterAccess Veteran
Posts: 401
From: Olympia, WA
Hi All,

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;


Thanks for taking a look at this,

Robert
Go to the top of the page
 
+
grazird
post May 15 2008, 04:01 PM
Post #2

UtterAccess Veteran
Posts: 401
From: Olympia, WA
Oops... the attachment didn't make it through. Here it is...
Attached File(s)
Attached File  untitled.JPG ( 116.66K ) Number of downloads: 3
 
Go to the top of the page
 
+
grazird
post May 16 2008, 12:22 PM
Post #3

UtterAccess Veteran
Posts: 401
From: Olympia, WA
Does anybody have any ideas/suggestions to fix this issue? Any help is greatly appreciated... feel free to ask for more details, if needed...

Robert
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 06:56 AM