Query Updating the Wrong Thing!! 1

[quest4]

I have a select query, that works with a function and I don't understand why I am getting the results I am. This is a section of the query that has the problem:

IIf([JuryVacCount]>2,(GetNumWeeks()+1))
FROM tblEmployees AS Emp LEFT JOIN tblAttendance AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE (Emp.EmpStatusType="Active"
And Emp.PayType="per Hour&quot
And (Att.DateWeekStarting Between Nz([Enter Start Date],DateAdd("ww",-1-GetNumWeeks(),Date()))
And Dateadd("ww",GetNumWeeks(),Nz([Enter Start Date],
DateAdd("ww",-1-GetNumWeeks(),Date()))))

This is the function:

Function GetNumWeeks(Optional num As Integer)
If num = 0 Then
GetNumWeeks = 13
Else
GetNumWeeks = num
End If
End Function
What it is supposed to do is increase the Function variable from 13 to 14. What it is doing is at the end of the row, where JuryVacCount>2, it put a 14 in a new column. Could someone please tell me what is wrong with my query and/or function? Also how to reset the GetNumWeeks() by to the default when a new employee number comes along. I am ready to pull my hair out with this one. I am inches away from finishing this database and this is the only thing holding me back, so thank you very much in advance to anyone who comes up with the answer.

 

[tlbroadbent]

The query is just doing what you told it to do. You included the IIf function in the select list so Access returns the output of the function as part of the result set.

Try this.

1) Remove the IIF(...) from the select list.
2) Change the GetNumWeeks function as follows.

Function GetNumWeeks(iJVC As Integer, Optional num As Integer)
Dim iAdj as Integer
If iJVC > 2 Then iAdj=1

If num = 0 Then
GetNumWeeks = 13 + iAdj
Else
GetNumWeeks = num + iAdj
End If

End Function

In the query change every occurence of GetNumWeeks() to GetNumWeeks([JuryVacCount]). Let me know if it works for you. 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]

Inching ever closer and closer. The first EmployeeNumber, who has a vacation week, worked perfevctlylisting out 14 weks of data. The second EmployeeNumber, who just had 13 weeks perfect attendence, also list out 14 weeks of data. How do I add a reset to default when the EmployeeNumber changes? I think take will do the trick. Ever be so close to the end of something that you can taste it, but can't reach it? Thank you again for all of the help that you have given me, I have learn alot from you and everyone else,

 

[quest4]

Oh I foegot to mention that I am also get a pop-up window asking me to enter JuryVacCount. Thanks.

 

[tlbroadbent]

The code I suggested does precisely what yo want in Access 97 and 2000. Did you enter the code as suggested - in the function and in the query. 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]

Yes i did, in fact I copied it and pasted it. See, I did learn from my past with you. I did remove that one line, IIf([JuryVacCount].2 ..., and I changed in the query all of the occurances, three of them, from GetNumWeeks() to GetNumWeeks([JuryVacCount], as you told me to. Could it be that iJVC not being iJuryVacCount? I don't know, I am just reaching, but that pop-up window asking for JuryVacCount should be some kind of clue, should it? Also I am not sure exactly what num is doing. Please let me know if you spot anything that I did wrong. Thanks you again foer everything.

 

[quest4]

Just for grins here is the quey with the changes:

IIf([JuryVacCount]<3,([DayCount]+[JuryVacCount]),0)
AS PerfectDayCount
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-GetNumWeeks([JuryVacCount]),Date())) And Dateadd(&quot;ww&quot;,GetNumWeeks([JuryVacCount]),Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-GetNumWeeks([JuryVacCount]),
Date()))))

Here is the new function:
Function GetNumWeeks(iJVC As Integer, Optional num As Integer)
Dim iAdj As Integer
If iJVC > 2 Then iAdj = 1

If num = 0 Then
GetNumWeeks = 13 + iAdj
Else
GetNumWeeks = num + iAdj
End If

End Function

I tried deleting the function and completely exited Access and then went back in and rebuilt the modGetNumWeeks module and I even recopied it have the forum. The same thing, no reset to the default, which is 13 and I still get a pop-up window asking for JuryVacCount. Thanks again for everything, it is getting late here and it think I will ponder this over a beer, where for somereason I do my best work.

 

[tempclerk]

1) Remove the IIF(...) from the select list.

 

[tlbroadbent]

Does your table contain a column JuryVacCount? If not where does that value come from? 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]

My table does not contain JuryVacCount, here is the whole first query:
SELECT Att.EmployeeNumber, Att.DateWeekStarting,
IIf(Nz([WorkDay1Reason])=&quot;&quot;,1,0) AS D1,
IIf(Nz([WorkDay2Reason])=&quot;&quot;,1,0) AS D2,
IIf(Nz([WorkDay3Reason])=&quot;&quot;,1,0) AS D3,
IIf(Nz([WorkDay4Reason])=&quot;&quot;,1,0) AS D4,
IIf(Nz([WorkDay5Reason])=&quot;&quot;,1,0) AS D5,
[D1]+[D2]+[D3]+[D4]+[D5] AS P_DayCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;HolD-A&quot;,1,0) AS H1,
IIf(Nz([Att.WorkDay2Reason])=&quot;HolD-A&quot;,1,0) AS H2,
IIf(Nz([Att.WorkDay3Reason])=&quot;HolD-A&quot;,1,0) AS H3,
IIf(Nz([Att.WorkDay4Reason])=&quot;HolD-A&quot;,1,0) AS H4,
IIf(Nz([Att.WorkDay5Reason])=&quot;HolD-A&quot;,1,0) AS H5,
[H1]+[H2]+[H3]+[H4]+[H5] AS H_DayCount,
[P_DayCount]+[H_DayCount] AS DayCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;JurD-A&quot;,1,0) AS J1,
IIf(Nz([Att.WorkDay2Reason])=&quot;JurD-A&quot;,1,0) AS J2,
IIf(Nz([Att.WorkDay3Reason])=&quot;JurD-A&quot;,1,0) AS J3,
IIf(Nz([Att.WorkDay4Reason])=&quot;JurD-A&quot;,1,0) AS J4,
IIf(Nz([Att.WorkDay5Reason])=&quot;JurD-A&quot;,1,0) AS J5,
[J1]+[J2]+[J3]+[J4]+[J5] AS JuryCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;VacD-A&quot;,1,0) AS V1,
IIf(Nz([Att.WorkDay2Reason])=&quot;VacD-A&quot;,1,0) AS V2,
IIf(Nz([Att.WorkDay3Reason])=&quot;VacD-A&quot;,1,0) AS V3,
IIf(Nz([Att.WorkDay4Reason])=&quot;VacD-A&quot;,1,0) AS V4,
IIf(Nz([Att.WorkDay5Reason])=&quot;VacD-A&quot;,1,0) AS V5,
[V1]+[V2]+[V3]+[V4]+[V5] AS VacCount,
[JuryCount]+[VacCount] AS JuryVacCount,
IIf([JuryVacCount]<3,([DayCount]+[JuryVacCount]),0) AS PerfectDayCount
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-GetNumWeeks([JuryVacCount]),Date()))
And Dateadd(&quot;ww&quot;,GetNumWeeks([JuryVacCount]),Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-GetNumWeeks([JuryVacCount]),
Date()))))
ORDER BY Att.EmployeeNumber;
Then there is a second to finalize it and here it is:
SELECT EAB1.EmployeeNumber,
Emp.NameFirst, Emp.NameInitial,
Emp.NameLast, Count(EAB1.EmployeeNumber) AS RecCnt
FROM qryEmployeeAttendanceBonuses1 AS EAB1
LEFT JOIN tblEmployees AS Emp ON Emp.EmployeeNumber=EAB1.EmployeeNumber
WHERE (((EAB1.PerfectDayCount)=5))
GROUP BY EAB1.EmployeeNumber, Emp.NameFirst, Emp.NameInitial, Emp.NameLast
HAVING COUNT(EAB1.EmployeeNumber)=13
ORDER BY EAB1.EmployeeNumber;
My table consists of EmployeeNumber DateWeekStarting WorkDay1 WorkDay1Reason WorkDay2 WorkDay2Reason WorkDay3 WorkDay3Reason WorkDay4 WorkDay4Reason WorkDay5 WorkDay5Reason. The function is listed above. Also I change 3 of the 5 VacD-A reasons to &quot;&quot; and it still is list out 14 weeks, it should have only done 13 weeks. I hope that this helps, I will try and see if I can stumble in to something here. Thanks you again for the help.

 

[quest4]

Dumb question time, I my effort to try an understand what this function is doing exactly, what is num? What is it doing? And last of all, how does the function know what iJCV is? Thanks for everything?

 

[quest4]

FYI, I made a small change to the function to this:
Function GetNumWeeks(iJVC As Integer, Optional num As Integer)
Dim iAdj As Integer
If (iJVC = 3 Or iJVC = 4 Or iJVC = 5) Then iAdj = 1

If num = 0 Then
GetNumWeeks = 13 + iAdj
Else
GetNumWeeks = num + iAdj
End If

End Function
The result was it started to print 13 weeeks again, but for everyone, including the foirst employee, who has a JuryVacCount of 5 in one week, which means there should have been 14 weeks of data for him. I still done understand the use of num in this and help is being no help at all. Thanks for all of your help, I hope to end this soon.

 

[tlbroadbent]

Let me try to answer the questions and then steer you in a slightly different direction.

1) num is an optional parameter in the GetNumWeeks function. If you want you can use the 2nd parameter to use a different number of weeks than 13.

2) You need to pass a value of iJVC to the function in the function call. Assuming that JuryVacCount was a column in your table, I had you use that as the value for the iJVC parameter. Unfortunately, JuryVacCount is a calculated value. As in my previous explanation, the value doesn't exist when the query is analyzed so zero is passed to the function.

You have a couple of options.

Option 1) Save the query with only the first two criteria of the where clause. Then create a new query that selects from the first query and includes the remainder of the where clause. By doing this you can verify the first query returns the correct results. JuryVacCount will exist and thus Access will be able to evaluate the value of the parameter passed to the function in the 2nd query.

Option 1: First query - save as qryJuryVacCount

SELECT Att.EmployeeNumber, Att.DateWeekStarting,
IIf(Nz([WorkDay1Reason])=&quot;&quot;,1,0) AS D1,
IIf(Nz([WorkDay2Reason])=&quot;&quot;,1,0) AS D2,
IIf(Nz([WorkDay3Reason])=&quot;&quot;,1,0) AS D3,
IIf(Nz([WorkDay4Reason])=&quot;&quot;,1,0) AS D4,
IIf(Nz([WorkDay5Reason])=&quot;&quot;,1,0) AS D5,
[D1]+[D2]+[D3]+[D4]+[D5] AS P_DayCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;HolD-A&quot;,1,0) AS H1,
IIf(Nz([Att.WorkDay2Reason])=&quot;HolD-A&quot;,1,0) AS H2,
IIf(Nz([Att.WorkDay3Reason])=&quot;HolD-A&quot;,1,0) AS H3,
IIf(Nz([Att.WorkDay4Reason])=&quot;HolD-A&quot;,1,0) AS H4,
IIf(Nz([Att.WorkDay5Reason])=&quot;HolD-A&quot;,1,0) AS H5,
[H1]+[H2]+[H3]+[H4]+[H5] AS H_DayCount,
[P_DayCount]+[H_DayCount] AS DayCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;JurD-A&quot;,1,0) AS J1,
IIf(Nz([Att.WorkDay2Reason])=&quot;JurD-A&quot;,1,0) AS J2,
IIf(Nz([Att.WorkDay3Reason])=&quot;JurD-A&quot;,1,0) AS J3,
IIf(Nz([Att.WorkDay4Reason])=&quot;JurD-A&quot;,1,0) AS J4,
IIf(Nz([Att.WorkDay5Reason])=&quot;JurD-A&quot;,1,0) AS J5,
[J1]+[J2]+[J3]+[J4]+[J5] AS JuryCount,
IIf(Nz([Att.WorkDay1Reason])=&quot;VacD-A&quot;,1,0) AS V1,
IIf(Nz([Att.WorkDay2Reason])=&quot;VacD-A&quot;,1,0) AS V2,
IIf(Nz([Att.WorkDay3Reason])=&quot;VacD-A&quot;,1,0) AS V3,
IIf(Nz([Att.WorkDay4Reason])=&quot;VacD-A&quot;,1,0) AS V4,
IIf(Nz([Att.WorkDay5Reason])=&quot;VacD-A&quot;,1,0) AS V5,
[V1]+[V2]+[V3]+[V4]+[V5] AS VacCount,
[JuryCount]+[VacCount] AS JuryVacCount,
IIf([JuryVacCount]<3,([DayCount]+[JuryVacCount]),0) AS PerfectDayCount
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

Option 1: 2nd query

Select * From qryJuryVacCount
Where Att.DateWeekStarting Between Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-GetNumWeeks([JuryVacCount]),Date()))
And Dateadd(&quot;ww&quot;,GetNumWeeks([JuryVacCount]),Nz([Enter Start Date],DateAdd(&quot;ww&quot;,-1-GetNumWeeks([JuryVacCount]),
Date())))
ORDER BY EmployeeNumber;

Option 2) Wrap the first query in an outer query. I can show you how this is done but I prefer Option 1 for simplicity. 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]

Thank for the response, I made your recommended changes and it produce the look of the old first query except that it listed both employees as 13 weeks instead of one at 13 weeks of data and one at 14 weeks of data because the first employee has one week were his JuryVacCount is 5, mean he took a vacation or something. Also the pop-up window when away, the one that asked for JuryVacCount. In looking at the first query I see it is listing everything, but a quick thought here is that this database will soon be populated with a hundred or so employees and will have 2 years of data, 104 weeks per employee. That is why I put the date criteria in the first query, to knock out all of the unneeded data and speed things up. Am I on the wrong track with that thinking? This query seems to want to lock onto one time period and not change, I am try to figure out where the reset, back to default, of GetNumWeeks is suppose to occur. I did copy and paste this in and I added a &quot;;&quot; to the end of the first query. Thanks you again for all of your help and assistance.

 

[quest4]

Another dumb question, what is the else GetNumber = num + iAdj supposed to be doing? I think I under stand the If line but I really can't figure the purpose of the else line. Thanks again for everything.

 

[tlbroadbent]

It is there if you enter a value for num. The else will use that value instead of 13. GetNumWeeks returns the sum of num + iAdj if it receives a value other than 0 for num. 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 see said the bind man. Any idea wht I am not getting that extra week of data on the first employee? Under the old queries, I get 14 weeks of data, whether they meet the criteria or not, JuryVacCount>2 and the first employee has one week where his JuryVacCount is 5. In the new queries I get 13 weeks of data for both employees againregardless of whether they meet the criteria or not. In both the old first query and the second new query, for the week of vaction for the first employee, the JuryVacCount is 5 and the PerfectDayCount is 0, which is correct. But because of the JuryVacCount being 5, it was suppoesed to get another week of data, 14 weeks in all and the reset to the 13 weeks for the next employee. So close, but yet so far away. I have tried everything else, maybe I should go to a church and try lighting a 100 candles. Weel thank you again for everything.

 

[quest4]

May it is time to give up on the course, unlees you see something that I am missing. How does that outer wrap of the first query work? I saved the original two queries under a different name. And I already got copies of these two, too. Please let me know what I need to do. Thanks a million for everyting.

 

[tlbroadbent]

It might be easier to see what is happening if I could look at your database. Any chance you could ZIP it and email it to me or place it online in a location where I can download it? 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]

No Problem, Give Me an E-Mail address and it will be there in a nano secon. Thanks.