Identical Query by With Different Result! Why?

[joesmithf1]

Hi,

I have a query which I run in the query design view. Then I have the same exact query which I run on the 'background' of a form.

The issue is, both queries turn out to have different record counts(eventhough I am using the same query and same tables). What is going on? Please help.

Thank you.

Joe

 

[pbaldy]

The exact same query should return the exact same records. Make sure the form doesn't have a filter applied.

 

[joesmithf1]

I may have failed to mention that the 'run on background' i mentioned earlier meant that I am running VBScripts on the form. Here are my codes; query design view and then script 'behind' the form.

Query Design View:SELECT DEDPARMS1.EMP_ID, DEDPARMS1.FORMAT_NM, First(DEDPARMS1.DEDPLAN_CD) AS FirstOfDEDPLAN_CD, First(DEDPARMS1.DEDTYPE_CD) AS FirstOfDEDTYPE_CD, Sum(IIf(Right([Dedparms1.Dedtype_CD],1)="R",[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([Dedetail1.Dedtype_CD],1)="R",[dedetail1.ded_am],0)) AS [Employer Actl], Sum(IIf(Right([Dedparms1.Dedtype_CD],1)="A",[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([Dedetail1.Dedtype_CD],1)="A",[dedetail1.ded_am],0)) AS [Admin Actl], Sum(IIf(Right([Dedparms1.Dedtype_CD],1)="E",[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([Dedetail1.Dedtype_CD],1)="E",[dedetail1.ded_am],0)) AS [Employee Actl], First(DEDPARMS1.STATUS) AS FirstOfSTATUS, First(DEDPARMS1.AGENCY) AS FirstOfAGENCY, First(DEDPARMS1.ORG) AS FirstOfORG, First(DEDPARMS1.TITLE) AS FirstOfTITLE, First(DEDPARMS1.STTL) AS FirstOfSTTL, First(Right(dedparms1.title,2)) AS RepUnit, First(Left([dedparms1.DEDTYPE_CD],2)) AS Type, First(Left([dedparms1.DEDTYPE_CD],2)) AS LeftType, First(DEDPARMS1.DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, First(Right([dedparms1.DEDPlan_CD],2)) AS Tier, First(Left([dedparms1.DEDPlan_CD],2)) AS Carrier, Plan.PlanDesc, Plan.TypeDesc
FROM (DEDPARMS1 LEFT JOIN DEDETAIL1 ON (DEDPARMS1.DEDPLAN_CD = DEDETAIL1.DEDPLAN_CD) AND (DEDPARMS1.FORMAT_NM = DEDETAIL1.FORMAT_NM) AND (DEDPARMS1.DEDTYPE_CD = DEDETAIL1.DEDTYPE_CD) AND (DEDPARMS1.EMP_ID = DEDETAIL1.EMP_ID)) LEFT JOIN Plan ON (DEDPARMS1.DEDPLAN_CD = Plan.Plan) AND (DEDPARMS1.HLTH_TYPE = Plan.Type)
GROUP BY DEDPARMS1.EMP_ID, DEDPARMS1.FORMAT_NM, Plan.PlanDesc, Plan.TypeDesc
HAVING (((First(DEDPARMS1.STATUS)) Not In ('P','S','X','O','E')) AND ((First(Left([dedparms1.DEDTYPE_CD],2))) In ('01')) AND ((First(Right([dedparms1.DEDPlan_CD],2)))<>"00" And (First(Right([dedparms1.DEDPlan_CD],2)))<>"17"))
ORDER BY DEDPARMS1.EMP_ID, First(DEDPARMS1.STATUS);

FORM SCRIPT VIEW("payp" in this case is 1, so this means I am grabbing the data from the same tables as above)
SQL = "SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, First(DEDPARMS" & payp & ".DEDPLAN_CD) AS FirstOfDEDPLAN_CD, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='R',[Dedetail" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='A',[Dedetail" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='E',[Dedetail" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, First(DEDPARMS" & payp & ".ORG) AS FirstOfORG, First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, " & _
"First(DEDPARMS" & payp & ".STTL) AS FirstOfSTTL, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS Type, " & _
"First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS LeftType, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, Plan.PlanDesc, Plan.TypeDesc " & _
"FROM (DEDPARMS" & payp & " LEFT JOIN Dedetail" & payp & " ON (DEDPARMS" & payp & ".DEDPLAN_CD = Dedetail" & payp & ".DEDPLAN_CD) AND (DEDPARMS" & payp & ".FORMAT_NM = Dedetail" & payp & ".FORMAT_NM) AND (DEDPARMS" & payp & ".DEDTYPE_CD = Dedetail" & payp & ".DEDTYPE_CD) AND (DEDPARMS" & payp & ".EMP_ID = Dedetail" & payp & ".EMP_ID)) LEFT JOIN Plan ON (DEDPARMS" & payp & ".DEDPLAN_CD = Plan.Plan) AND (DEDPARMS" & payp & ".HLTH_TYPE = Plan.Type) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Plan.PlanDesc, Plan.TypeDesc " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P','S','X','O','E')) AND ((First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2))) In ('01')) AND ((First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'00' And (First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'17')) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"


I do NOT have any kind of filter on my form.

 

[pbaldy]

Put in a Debug.Print on the SQL string and see if they are truly the same when it's run.

 

[joesmithf1]

Hi,
Thanks Paul. Where would I insert this "debug.print" command in the SQL string? And would I put it in both the Query Design veiw and the Script view?

 

[pbaldy]

I'm sorry, that's a VBA command. Put it in right after you set the SQL string, like:

SQL = "SELECT..."

Debug.Print SQL

When you run the code, it will print the final SQL string (after concatenating your variables) out to the Immediate window, where you can examine it or copy it to a blank query and examine it there. We're looking to see if the string produced by the VBA is indeed identical to the saved query.

 

[joesmithf1]

Got it! Thanks, Paul!

Leon