IsNull - HALP

Erin M 2021

Member
Local time
Yesterday, 18:39
Joined
Apr 14, 2021
Messages
77
=[Commitments]/((iif([FY S&S].[Report]![S&S#Commitments] isnull,0,[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments])
 
ISNULL is for query criteria

in vb code / formulas: IsNull(field)

iif(IsNull([FY S&S].[Report]![S&S#Commitments]),0,[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments]
 
if S&S#Commitments is null then you are providing a value of 0. Wont that result in a division by zero error?

edit: Terrible field name by the way.
 
if S&S#Commitments is null then you are providing a value of 0. Wont that result in a division by zero error?

edit: Terrible field name by the way.
S&S#Commitments + TotalCommitmentswould not total zero
 
ISNULL is for query criteria

in vb code / formulas: IsNull(field)

iif(IsNull([FY S&S].[Report]![S&S#Commitments]),0,[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments]
Thanks, I'm still receiving the #Type! error with this.
 
=[Commitments]/((iif(IsNull([S&S#Commitments]) OR [S&S#Commitments] = 0 ,0, [S&S#Commitments]+Nz([TotalCommitments],0))

Hope I got the parens right. Ditto on the names. Really poor practice. You are working with a relational database NOT a spreadsheet. The user doesn't see the internal names so you should cater to VBA when naming objects and leave the special characters and embedded spaces for labels on forms and reports.
 
=[Commitments]/((iif([FY S&S].[Report]![S&S#Commitments] isnull,0,[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments])

Unless I'm mistaken that's a division operator after [Commitments]. Your iif statement supplies a 0 if the iif criteria is true.
 
I knew that but forgot to move the Iff() thanks for the reminder.

= iif(IsNull([S&S#Commitments]) OR [S&S#Commitments] = 0 ,0, [Commitments] / ([S&S#Commitments]+Nz([TotalCommitments],0))

This assumes you want to return 0 if [S&S#Commitments] is null or zero. If you want to return [Commitments] or do a different calculation, you need to expand the expression. We can't read minds.
 
Had the original backwards. I've attempted this: =[Commitments]/((iif([FY S&S].[Report]![S&S#Commitments] isnull,[TotalCommitments],[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments])

When [FY S&S].[Report]![S&S#Commitments] is zero, I get the #Type! error. When it is not zero, this report works properly.

Appreciate the feedback on naming. I am self taught and just making it work. What is meant by the comment to 'cater to VBA'?
 
What is meant by the comment to 'cater to VBA'?
There are many do's and don'ts when it comes to naming things in access/vba.
Don't use any spaces, special characters, reserved names, etc.
Users do not see much of what's behind the scenes like table names, field names, variables, etc. so its best to cater to what vba expects to see rather than what the user sees. For instance "Name" Name is a reserved word so to use it you would prefix it like txtName. Your user doesn't see this but the label for the textbox can have the caption "Name" which the user does see. Hope this makes sense.
 
When [FY S&S].[Report]![S&S#Commitments] is zero, I get the #Type! error. When it is not zero, this report works properly.
That is why you move the test for 0/null OUTSIDE of the calculation. Then you only do the calculation if you are NOT going to divide by zero. Look at my example in #9 again.

IIf (is the field null or zero, true value, calculation)
 

Users who are viewing this thread

Back
Top Bottom