using query as DAO.recordset showing incorrect results

[grades]

Hi,
I have what seems to be a pretty complicated issue so I'll try and explain it as best i can.

I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.
Here is where I call the query:

Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
 qdf.Parameters(0) = Forms!frmmain!dt1.Value
 qdf.Parameters(1) = Forms!frmmain!dt2.Value
 qdf.Parameters(2) = Forms!frmmain!d2.Value
 qdf.Parameters(3) = Forms!frmmain!probationvar
 qdf.Parameters(4) = Forms!frmmain!s1.Value
 qdf.Parameters(5) = Forms!frmmain!d1.Value
 qdf.Parameters(6) = Forms!frmmain!errnumvar
 qdf.Parameters(7) = Forms!frmmain!site.Value
 qdf.Parameters(8) = Forms!frmmain!site2.Value
 qdf.Parameters(9) = Forms!frmmain!site3.Value
 qdf.Parameters(10) = Forms!frmmain!site4.Value
 qdf.Parameters(11) = Forms!frmmain!site5.Value
 qdf.Parameters(12) = Forms!frmmain!errpercvar
 
Set rs = qdf.OpenRecordset
 
rs.MoveFirst
Do While Not rs.EOF
Select Case rs.Fields("max")
 Case 1
     strReport = "Errors1"
     Case 2
     strReport = "Errors2"
     Case 3
     strReport = "Errors3"
     Case Else
    strReport = "Errors0"
  End Select
    DoCmd.OpenReport strReport, , , "employeeID=" & rs!employeeID
   rs.MoveNext
Loop
Set rs = Nothing
Exit Sub

Let me explain.. The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.

The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.

I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
Any help would be appreciated, please let me know if you require any more info.
THanks,

 

[mdlueck]

You should provide the SQL for the query.

Does the SQL have logic to handle the specilal logic to skip records in this state...

The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.

I am guessing that the filter conditions are missing since you say the query includes rows which should be skipped.

 

[grades]

Let me clarify, the query doesn't include "rows" that should not be present, the query looks great when I view it. But when that VBA code runs, what I am seeing is data in a field called "max" is incorrect. I am not using the Filter condition in SQL, I am using other queries to calculate this "max" data which are then called upon by my final query here.
If I were to add

MsgBox  "max: " & rs.Fields("max")

within the loop, it will show me not necessarily what is displayed by that same field in that query (as stated in my above condition). Somehow it gathers data which was purposely overlooked in another query and adds it up.

Here is query 1:
The purpose is to grab the maximum value of the warningLevel for a person. the conditions in english are 1. if the person is on probation
{DateDiff("d",[hiredate],[forms]![frmmain]![d2])<=[forms]![frmmain]![probationvar]}
Then the only letters counted are within the probation period
{<([hiredate]+[forms]![frmmain]![probationvar]) And >[forms]![frmmain]![s1]}
and 2. if the person is off probation, then the letters counted are past the probation period
>([hiredate]+[forms]![frmmain]![probationvar])
AND more recent that six months ago
>[forms]![frmmain]![s1]

If you can understand that

SELECT agent_letters.employeeID, Max(agent_letters.warningLevel) AS MaxOfwarningLevel, agent_info.hireDate
FROM agent_info INNER JOIN (letter_types INNER JOIN agent_letters ON letter_types.ID = agent_letters.letterType) ON agent_info.employeeID = agent_letters.employeeID
WHERE (((DateDiff("d",[hiredate],[forms]![frmmain]![d2]))>[forms]![frmmain]![probationvar]) AND ((agent_letters.letterDate)>([hiredate]+[forms]![frmmain]![probationvar]) And (agent_letters.letterDate)>[forms]![frmmain]![s1])) OR (((DateDiff("d",[hiredate],[forms]![frmmain]![d2]))<=[forms]![frmmain]![probationvar]) AND ((agent_letters.letterDate)<([hiredate]+[forms]![frmmain]![probationvar]) And (agent_letters.letterDate)>[forms]![frmmain]![s1]))
GROUP BY agent_letters.employeeID, agent_info.hireDate, letter_types.ID
HAVING (((Max(agent_letters.warningLevel)) Is Not Null) AND ((letter_types.ID)=2)) OR (((Max(agent_letters.warningLevel)) Is Not Null) AND ((letter_types.ID)=2));

When I view this query, it gives no results for a certain test case I am looking at. It shouldnt, because the test case had a letter within the probation period, but is now off probation - letter is dismissed.

The next query calls on the above for that value. The test case shows up in the 2nd query because they need to get a letter, but the "max" value is blank. Great, that's what I want.
So when I run the VBA script to print them a letter it shows that the "max" is 1, as if they were still on probation. Why is it showing me a 1 when the query has it blank? I did try to use Iif statements in the query to insert a 0 in case my loop of If's were messed up, but that did not help.

I am pulling my hair out. I dont even really know where to continue troubleshooting this. Am I calling the recordsource wrong? Should I save the results to a table first?
The query that is acting screwy is here:

SELECT [7-ErrorsList].[Payroll ID], [7-ErrorsList].[payroll id] AS employeeID, [firstname] & " " & [lastname] AS rname, Max([7-ErrorsWarningLevel].MaxOfwarningLevel) AS [max], [7-ErrorsVerbalDate].evd, [7-ErrorsWrittenDate].ewd, [7-ErrorsFinalDate].efd, [7-ErrorsList].CountOfemployeeID, [7-ErrorsList].site, LatestKPI.calls, Round([CountOfemployeeID]/[calls]*100,2) AS eperc, [7-ErrorsList].hireDate
FROM ([7-ErrorsList] LEFT JOIN ((([7-ErrorsWarningLevel] LEFT JOIN [7-ErrorsVerbalDate] ON [7-ErrorsWarningLevel].employeeID = [7-ErrorsVerbalDate].employeeID) LEFT JOIN [7-ErrorsWrittenDate] ON [7-ErrorsWarningLevel].employeeID = [7-ErrorsWrittenDate].employeeID) LEFT JOIN [7-ErrorsFinalDate] ON [7-ErrorsWarningLevel].employeeID = [7-ErrorsFinalDate].employeeID) ON [7-ErrorsList].[Payroll ID] = [7-ErrorsWarningLevel].employeeID) LEFT JOIN LatestKPI ON [7-ErrorsList].[Payroll ID] = LatestKPI.employeeID
WHERE (((LatestKPI.KPIdate) Between [forms]![frmmain]![d1] And [forms]![frmmain]![d2]))
GROUP BY [7-ErrorsList].[Payroll ID], [7-ErrorsList].[payroll id], [firstname] & " " & [lastname], [7-ErrorsVerbalDate].evd, [7-ErrorsWrittenDate].ewd, [7-ErrorsFinalDate].efd, [7-ErrorsList].CountOfemployeeID, [7-ErrorsList].site, LatestKPI.calls, Round([CountOfemployeeID]/[calls]*100,2), [7-ErrorsList].hireDate
HAVING (((Max([7-ErrorsWarningLevel].MaxOfwarningLevel)) Is Null Or (Max([7-ErrorsWarningLevel].MaxOfwarningLevel))=0 Or (Max([7-ErrorsWarningLevel].MaxOfwarningLevel))=1 Or (Max([7-ErrorsWarningLevel].MaxOfwarningLevel))=2 Or (Max([7-ErrorsWarningLevel].MaxOfwarningLevel))=3) AND (([7-ErrorsList].CountOfemployeeID)>=[forms]![frmmain]![errnumvar]) AND (([7-ErrorsList].site)=[forms]![frmmain]![site] Or ([7-ErrorsList].site)=[forms]![frmmain]![site2] Or ([7-ErrorsList].site)=[forms]![frmmain]![site3] Or ([7-ErrorsList].site)=[forms]![frmmain]![site4] Or ([7-ErrorsList].site)=[forms]![frmmain]![site5]) AND ((LatestKPI.calls)>0) AND ((Round([CountOfemployeeID]/[calls]*100,2))>=[forms]![frmmain]![errpercvar]))
ORDER BY [firstname] & " " & [lastname];

 

[grades]

Nevermind, I just set it as a make-table query and using the table a recordsource is a good workaround.