IsNull - HALP

[Erin M 2021]

=[Commitments]/((iif([FY S&S].[Report]![S&S#Commitments] isnull,0,[FY S&S].[Report]![S&S#Commitments]))+[TotalCommitments])

 

[Gasman]

IsNull function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Ranman256]

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]

 

[moke123]

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.

 

[Erin M 2021]

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

 

[Erin M 2021]

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.

 

[moke123]

=[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.

 

[Erin M 2021]

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'?

 

[moke123]

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.