Should this be a Function or Part of a Query???????? 1

[quest4]

This is the bottom 1/3 of a query in Access2000. It is not working correctly and I don't know if it ever will. Here is my code:

Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE IIf((Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=3)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=4)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=5)), GetNumWeeks() + 1 And RecCnt +0)

My question is, should this be a function? If so, any pointers would be greatly appreciated, I'm not very at VB coding yet. If it should stay as is in the query, my am I getting syntax errors? Thanks to anyone who may give me a helping hand.

My quest

 

[tempclerk]

The reason you are getting syntax errors is that an IIf is a choice, not an action. What your IIf is saying is that if any of your conditions are true, select EmployeeNumber and DateWeekStarting where "GetNumWeeks() + 1 And RecCnt +0" which makes no sense.

I am not absolutely sure, but I don't think you can change the value of a variable in the midst of a query. I'm sure it can be done in a module, but as I don't yet "do code" I couldn't tell you how. What I have been thinking is along the lines of what MichaelRed tried suggesting to you a couple of weeks ago. You should use the criteria you have developed so far to define each week per employee as either bonus eligible, no bonus, or skip. Then you can select the most recent 13 weeks that are not designated skip, and if none of them are are designated "no bonus", then Bonus is true. I haven't worked out exactly how to do this selection, but try looking into TOP VALUES, Numbering lines in query results, etc. and considering the progress you've made so far, you'll come up with the answer before I do!

 

[quest4]

What you stated is exactly what I want this beast to do. I f any one is true increase GetNumWeek() by one week and add 0 to RecCnt. Basically, it means look at the last 14 weeks and see if you get a count of 13 perfect weeks. It is called ignoring a vacation/jury duty week. Maybe I will see if there is anyone out there who can help me convert this section of the query to VB code. Thanks for the help.