AddsAn Extra Column instead of an Extra Entry?????? 1

[quest4]

I hope someone will look at this and tell me why it is working as it is and how to correct it.
GetNumWeeks() AS TimePeriod,
IIf([JuryVacCount]<3,([DayCount]+[JuryVacCount]), 0)
AS PerfectDayCount,
IIf([JuryVacCount]>2,([TimePeriod]+1))
FROM tblEmployees AS Emp LEFT JOIN tblAttendance AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE (Emp.EmpStatusType=&quot;Active&quot;
And Emp.PayType=&quot;per Hour&quot
And (Att.DateWeekStarting Between Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-TimePeriod,Date()))
And Dateadd(&quot;ww&quot;,TimePeriod,Nz([Enter Start Date],
DateAdd(&quot;ww&quot;,-1-TimePeriod,Date()))))
ORDER BY Att.EmployeeNumber;
The GetNumWeeks() is set to a default of 13 and insterad of picking it up, I get a pop-up window, so I enter manually 13 and with the one employeewho has a JuryVacCount>2, it creates a new column and puts the 14 there instead of listing 14 rows, and only lists only 13 rows. Why is it behaving like that? Why is is it not using TimePeriod? Thanks to anyone who answers this.

 

[tlbroadbent]

Access analyzes the criteria before creating the result set. Thus you have no column named (or aliased) as TimePeriod when Access begins to process your query. You will have to use the function in the place of the column name, TimePeriod. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

I understand what you are saying, but how do I add 1 to the value of GetNumWeeks(), increasing its' value from 13 to 14? Then how do I reset it to the default value, which is 13, when the next EmployeeNumber comes-up? I tried this, after changing all of the TimePeriods to GetNumber():
IIf([JuryVacCount]>2,(GetNumWeeks()+1))
I got the same results, I got a new column instead of 14 rows of data, I did get the 13 rows, though. If showing you the total query would help, please let me know. I have learned alot sense we last talked. Thanks you again for all of your help, with this finished I am done with this quert and I am of to test land.