SQL query of MS Access table in VB6 not calculating results properly.... Is it a 'data type' issue?

Using: MS Access 2010; VB6 with MSHFlexGrid

I have an SQL query in VB6 that calls data from an MS Access table called 'Batting'. It's baseball data that tracks singles, doubles, triples, homers, walks, strikeouts, at-bats etc. All I want to do is calculate 'batting average' by aggregating all the different types of hits, and dividing them by at-bats. My SQL statement will aggregate the hits, but it's wrong in calculating the batting average. My guess is that it has to do with numerical values being treated as strings. I have no hair left to pull out---so I decided to post here. I have Total At-Bats [TAB], singles [SI], doubles [DB], triples [TR], and home runs [HRun] that I'm aggregating.

Here's the statement:
SqlString = "Select Batter, Team, SUM([AB]) As TAB, SUM([1B]) As SI, SUM([2B]) As DB, SUM([3B]) As TR, SUM(
) As HRun, (IIf(IsNull([SI]), 0, 1) + IIf(IsNull([DB]), 0, 1) + IIf(IsNull([TR]), 0, 1) + IIf(IsNull([HRun]), 0, 1)/TAB) AS BAvg From Batting GROUP BY Team, Batter"


I have a picture of the tabled data, the table design view, and the flexgrid results in VB6 that the query creates. Notice how every batting average result is 3.25 ..... this is despite the fact I have some code that rounds the values of the column off to .XXX numerical digit format. This result is really strange; not sure where I'm going wrong.

Any help is really appreciated.
mdb-tabled-data.jpg
mdb-design-view.jpg
flexgrid-results.jpg
LVL 4
jazjefAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Just a guess here. Try adding an extra set of brackets in your formula. One after the comma after HRun and the other before /TAB:

SqlString = "Select Batter, Team, SUM([AB]) As TAB, SUM([1B]) As SI, SUM([2B]) As DB, SUM([3B]) As TR, SUM(
) As HRun, ((IIf(IsNull([SI]), 0, 1) + IIf(IsNull([DB]), 0, 1) + IIf(IsNull([TR]), 0, 1) + IIf(IsNull([HRun]), 0, 1))/TAB) AS BAvg From Batting GROUP BY Team, Batter"

Flyster
0
jazjefAuthor Commented:
Well, that changed it alright Flyster----- now every BAvg is either 1.000, or .800  ..... but you're on to something here. Any other ideas?
0
FlysterCommented:
What about this:

SqlString = "Select Batter, Team, SUM([AB]) As TAB, SUM([1B]) As SI, SUM([2B]) As DB, SUM([3B]) As TR, SUM(
) As HRun, ((IIf(IsNull([SI]), 0, [SI]) + IIf(IsNull([DB]), 0, [DB]) + IIf(IsNull([TR]), 0, [TR]) + IIf(IsNull([HRun]), 0, [HRun]))/TAB) AS BAvg From Batting GROUP BY Team, Batter"
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

ThomasMcA2Commented:
Your averages are wrong because every iif...IsNull is returning either 0 or 1.

Here is the syntax for iif:
iif ( condition, value_if_true, value_if_false )

Open in new window


Change them like this:
IIf(IsNull([SI]), 0, [SI]) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FlysterCommented:
Looking at your IIf statements, If the field value was null, the aggregated value is 0. If not, then it's 1. It should be the field value. The extra brackets directs the order of calculation. Your original code was looking at it as [SI] + [DB] + [TR] + ([HRun]/TAB). You want: ( [SI] + [DB] + [TR] + [HRun] ) / TAB
0
jazjefAuthor Commented:
ha ha..... that was the other piece to the puzzle; nice work ThomasMcA2. Combined with Flyster's added parentheses it works great. I'll split the points since the statement won't run correctly without both solutions in place. Many thanks guys.
0
jazjefAuthor Commented:
Superb. Quickly solved with very clear explanation. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.