Same query different locations different results

[RayMilhon]

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

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.

[pbaldy]

What does the code-based version look like? At a guess, the SQL isn't coming out like you expect. You can use this to test:

BaldyWeb-Immediate window

Oh, and if you're using ADO, the wildcard needs to be %.

[June7]

Are you using this query to open ADO RecordSet?

I encountered this when constructing query in VBA that has wildcard. I was so frustrated I redesigned the query to eliminate wildcard. I later learned ADO uses different wildcards. Review http://www.techrepublic.com/article/...a-expressions/

Why are you using name as criteria? You want all John* Smit* records, even if more than one person named John* Smit*?

Why do you need query to get this aggregate calc? Is the raw data in the report RecordSource? Can't just use Avg() in textbox in report footer?

[RayMilhon]

June7 No the raw data for this field is in a different database and tables. Also the Source for this report is the members that need a certain examination done and the RAF Score is based on a different dataset. Only need the average of 1 field by Provider for this report. Unfortunately the dataset for this RAF Score does not have the same data elements and the Provider name is the only thing in common. Although I have to use only the left 4 of each as they are not exactly the same. The report data has the name as "SMITH M.D., JOHN
and the RAF Score has "Smith" in 1 column, "JOHN" in another column and "MD" in a third. None of our providers have a last name shorter than 4 characters so that is why I used that. I do use ADO and changing the * to % helped but there still quite a few that are coming through as 0 when the data is there and if I run the query in the query builder it returns the correct data. In VBA it's null.

[RayMilhon]

Thanks Paul, I'd forgotten the % for ADO. It helped but still not 100% I do get the data for some but not for all. still working on it.