Why would a query (a simple one at that) generate different results when executed in VBA as opposed to the query builder in Access 2010.
The query is
Code:
SELECT avg(dbo_V_iC_HVVMG_MemberRiskScore.CurrentYearRAF) AS RAFScore
FROM dbo_V_iC_HVVMG_MemberRiskScore
Where dbo_V_iC_HVVMG_MemberRiskScore.PCPLastName like "SMIT*" and dbo_v_ic_hvvmg_memberriskscore.pcpfirstname like "JOHN*";
If run in the query builder I get the expected result 1.559
When run in code I get Null.
yes dbo_v_ic_hvvmg_memberriskscore is a linked SQL Server 2008 R2 table.
It's running in VBA because the report is grouped by PCP and is run for each of the PCP's. This particular query populates a single field in the group footer. There are 2 other fields in the footer that perform similarly. 1 for Total PCP membership and 1 for the total number of Wellness Surveys done by the PCP. Both of those work just fine. This one worked but the method of retrieving the score took hours to run. I have access to a datawarehouse where the scores were already calculated so I revised the report to look there speeding up the report into a few minutes. (hopefully if I can get it to actually run correctly)
any help is appreciated.