Overflow error on query (1 Viewer)

clatham · Nov 5, 2018

Hi all,

I've started getting an overflow error on the following:

SELECT qrySessionsAttended.StudentID, qrySessionsAttended.TotalSessions, qrySessionsAttended.SessionsAttended, [SessionsAttended]/[TotalSessions] AS AttendancePercentage
FROM qrySessionsAttended;

I assume the error is being caused by [SessionsAttended] and [TotalSessions] occassionally = 0, but I'm not sure how to get around this. It is correct that the two fields may = 0 so how can I get Access to leave those records without trying to calculate 0/0?

:banghead:

plog · Nov 5, 2018

I make a SafeDivision function in a module and pass it the values I want to divide:

AttendancePercentage: SafeDivision(SessionsAttended, TotalSessions)

Code:

Function SafeDivision(in_Numerator, in_Denominator)
  ' performs division on 2 numbers it is passed as long as in_Denominator isn't 0--returns 0 for those values

ret= 0

if (in_Denominator<>0) ret = in_Numerator / in_Denominator

SafeDivision = ret


End Function

Of course you could cram that logic all into the calculated field in the query as well.

clatham · Nov 5, 2018

I'm afraid I know nothing about modules, so this might be a stupid question; do I change the in_Numerator and in_Denominator to my field names?

plog · Nov 5, 2018

Nope. The function and your query are seperate items. They know nothing of what's in the other--you pass the function values, it passes back a value.

So, you put your field names in the query field I first posted and the function gets those values by referencing the names they are given in the function (in_Numerator and in_Denominator).

clatham · Nov 5, 2018

I don't think I did it right, I'm getting a syntax error. Should I have named the module "SafeDivision"?

MajP · Nov 5, 2018

You can also do it inline with the iif checking for null or 0

Code:

SELECT 
  SessionsAttended, 
  TotalSessions, 
  IIf(Nz([TotalSessions],0)+[totalsessions]=0,"N/A",[sessionsattended]/[totalsessions]) AS PercentAtt
FROM Sometable

plog · Nov 5, 2018

A syntax error has to do with mistyped code. You can name the module itself anything you want, I would make it different than the function though.

Post your code. It looks like my initial code is missing a 'then' in it.

clatham · Nov 5, 2018

Qry Code:

SELECT qrySessionsAttended.StudentID, qrySessionsAttended.TotalSessions, qrySessionsAttended.SessionsAttended, SafeDivision([SessionsAttended],[TotalSessions]) AS AttendancePercentage
FROM qrySessionsAttended;

Module Code:
Function SafeDivision(in_Numerator, in_Denominator)
' performs division on 2 numbers it is passed as long as in_Denominator isn't 0--returns 0 for those values

ret = 0

if (in_Denominator<>0) ret = in_Numerator / in_Denominator

SafeDivision = ret

End Function

plog · Nov 5, 2018

if (in_Denominator<>0) then ret = in_Numerator / in_Denominator

Minty · Nov 5, 2018

Actually if you have saved the module with the same name as the function Access WILL barf.

Change the module name to modBasicFunctions or something similar.

clatham · Nov 6, 2018

Thank you both, the errors have now ceased.

Do I need to add something to the Module to get the field to display the calculation as a percent? In the qryAttendancePercentage the field format is set to percent, but I gather the Module used for the calculation supersedes this?

Minty · Nov 6, 2018

You can apply the format directly to the result , something like

Code:

PercResult: Format (SafeDivision(Field1,Field2),"Percent")

clatham · Nov 6, 2018

Sorry, this might be yet another stupid question - where do I put this code? in the qry sql or in the module?

Minty · Nov 6, 2018

Nope it's never a stupid question when you don't know the answer.
This would go in your query.

Leave the function to return a "raw" number, as you may not always want the result expressed as a percentage when you use it somewhere else.

clatham · Nov 6, 2018

PercResult: Format (SafeDivision([SessionsAttended]/[TotalSessions]),"Percent")

This is saying I have an expression with the incorrect number of arguments. What am I missing?

plog · Nov 6, 2018

Arguments are the values you pass to a function.
You seperate them by a comma when calling the function.
SafeDivision() takes 2 arguments (the numerator and the deonominator values).
Format() takes 2 arguments (the number and the format)

You have constructed your Format call correctly--the number comes from SafeDivision, the format is "Percent".

You have not constructed your SafeDivision call correctly--you are only passing it one value--there are no commas in your call.

It should be SafeDivision(x, y), not SafeDivision(x/y).

clatham · Nov 6, 2018

Thank you, I see what you mean. This now works exactly how I would expect it to.

Minty · Nov 6, 2018

A bit of "My Bad" there, I mistyped the original example I gave which didn't help...

Overflow error on query (1 Viewer)

clatham

Registered User.

plog

Banishment Pending

clatham

Registered User.

plog

Banishment Pending

clatham

Registered User.

MajP

You've got your good things, and you've got mine.

plog

Banishment Pending

clatham

Registered User.

plog

Banishment Pending

Minty

AWF VIP

clatham

Registered User.

Minty

AWF VIP

clatham

Registered User.

Minty

AWF VIP

clatham

Registered User.

plog

Banishment Pending

clatham

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread