Query overflow error

[Kat2016]

Hi,



I have a calculated field in my query that uses division. It is giving me an overflow error because I am dividing by zero in some entries.

My SQL is:

SELECT [Adjust Casino Retailers Activity 2016].activity_date, Sum([Adjust Casino Retailers Activity 2016].cash_played) AS SumOfcash_played, Sum([Adjust Casino Retailers Activity 2016].cash_won) AS SumOfcash_won, Sum([Adjust Casino Retailers Activity 2016].hand_pays) AS SumOfhand_pays, Sum([Adjust Casino Retailers Activity 2016].progressive_increment) AS SumOfprogressive_increment, Sum([Adjust Casino Retailers Activity 2016].net_sales) AS SumOfnet_sales, [Adjust Casino Retailers Activity 2016].WIN, Sum([Adjust Casino Retailers Activity 2016].[Due to AGLC]) AS [SumOfDue to AGLC], Sum([Adjust Casino Retailers Activity 2016].Commission) AS SumOfCommissionFROM [Adjust Casino Retailers Activity 2016]
GROUP BY [Adjust Casino Retailers Activity 2016].activity_date, [Adjust Casino Retailers Activity 2016].WIN;

I think I have to include somewhere in it that it should ignore entries that would be divided by zero, but I have no idea where to begin.

I am extremely new to Access, and would greatly appreciate if someone could help me out.

Thanks.

[CJ_London]

suggest as a priority learn to use aliasing or shorter names, will make your code much easier to read - see table properties in query builder

SELECT A.activity_date, Sum(A.cash_played) AS SumOfcash_played, Sum(A.cash_won) AS SumOfcash_won, Sum(A.hand_pays) AS SumOfhand_pays, Sum(A.progressive_increment) AS SumOfprogressive_increment, Sum(A.net_sales) AS SumOfnet_sales, A.WIN, Sum(A.[Due to AGLC]) AS [SumOfDue to AGLC], Sum(A.Commission) AS SumOfCommission FROM [Adjust Casino Retailers Activity 2016] AS A
GROUP BY A.activity_date, A.WIN;

You say you are dividing by zero but I don't see any divisions in what you have provided

the way to protect against division by zero errors is to use an iif statement

somevalue:iif(thisnum=0, 0, thatnum/thisnum)

you may also need to protect against nulls in which case

somevalue:iif(nz(thisnum,0)=0, 0, thatnum/thisnum)

[CJ_London]

one other thought - overflows usually mean trying to assign a bigger number to a smaller data type - integer for example covers whole numbers from -32767 to +32767 - so if you calculate a value outside this range say 40000 and assign it to the integer field it will overflow - the solutio is to change your number type from integer to long

[Kat2016]

I will suggest that to the lady I am helping.

WIN is a calculated field from my database. When I try to run a summarized query it gives me an overflow error. WIN entries usually are 0.5 - 1.9. I'm assuming that is where the issue originates.

Where exactly will the iff statement be entered into the SQL?

What does it mean by thisnumb or thatnumb? Must I replace those with specific numbers?

I'm sorry I am very new to this.

[Kat2016]

The calculation used for WIN is ( [cash_won] + [hand_pays] ) / [cash_played]

[CJ_London]

so the iif statement would look like this

iif([cash_played]=0,0,( [cash_won] + [hand_pays] ) / [cash_played])

note calculated fields in tables are not considered a good idea by many professionals, they can generate all sorts of unexpected problems - see this link

http://allenbrowne.com/casu-14.html

[Kat2016]

Thank you so much

One last question. Can that same thing be done to a summary query?

[Kat2016]

Actually scrap that question I got it.
Once again thank you, you have saved my very existence.