Error, because a field has no value (Access 2002 (Access Data Project))

Topic

New Reply

Hi ,
i am very new to Access, do not understand VB and would like to ask for your help.
Since some time now, i am learning and working on a Database (first “mdb” and later “adp”) for the accounting of my brothers small company.
In one form i am calculating the sum of several fields which come from several functions. The problem is, that if one field has no value, i can not do the calculation at all. Before i had one solution for one field using the if-function, but actually i have some more fields, which could end up with no value. Do i have to use the if-function to work around all those fields or is there another way?
I searched in this forum, but i do not really understand if the “Nz”-function will help here and how i could use it.

Although my code is quite a shame, i dare to paste it. Maybe, someone care to take a look?

Private Sub ErgebnisTextfeld_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If (Forms!frmBilanz!frmBSfrmfunDarlehen!Darlehen > 0) Then
Forms!frmBilanz!ErgebnisTextfeld = Forms!frmBilanz!frmBSfrmBankKasse!BAnfangsstand – Forms!frmBilanz!frmBSfrmfunVerbindlichkeiten!Gesamtkosten – Forms!frmBilanz!frmBSfrmfunBankanKasse!BankanKasse + Forms!frmBilanz!frmBSfrmfunForderungen!Gesamtpreis + Forms!frmBilanz!frmBSfrmfunDarlehen!Darlehen
Else
Forms!frmBilanz!ErgebnisTextfeld = Forms!frmBilanz!frmBSfrmBankKasse!BAnfangsstand – Forms!frmBilanz!frmBSfrmfunVerbindlichkeiten!Gesamtkosten – Forms!frmBilanz!frmBSfrmfunBankanKasse!BankanKasse + Forms!frmBilanz!frmBSfrmfunForderungen!Gesamtpreis
End If
End Sub

I am very sorry for my ugly code and long form names, it was more like an accident, that i actually have some code at all. And sorry again, for i do not know how to change the code, so it is not that unreadable long. : (

Dankesch

Reply | Quote

Replies

First things first. If your code is within the form you are working with, then you don’t have to use the full “forms!formname!controlname” designation all the time. You can use Me instead, as in “Me!controlname”.

The NZ function is what you want to use, but you have to use it on each element. NZ stands for Null-to-Zero, although you can actually have it substitute any value if a Null is detected. Use it like this:

myresult = NZ( Me!ControlA) + NZ( Me!ControlB) + etc…..

Reply | Quote

Ops! I knew that when i finally dare to post a question here, i would put shame on me.
But thank you very much for your help! Now my “code” is much simplier without all these if-functions and in addition i learned something more.

Unfortunately the fields are not in the same form.
The data i want to calculate is in subforms, which are based on functions, as i didn’t know how to base text fields on “queries”.

Thank you
Ines

Reply | Quote

You left out the substitution, Mark.

myresult = NZ( Me!ControlA,0) + NZ( Me!ControlB,0) + etc…..

Reply | Quote

Well, if Eileen could integrate the VBA editor into the messaging, I wouldn’t make these mistakes!

Reply | Quote

Just an FYI. The substitution argument is optional unless used in a query, so Mark’s code would have worked.

Jim.

Reply | Quote

It worked, though i wondered about the arguments of the function. So thanks to Mark and Charlotte.

Reply | Quote

But it isn’t as easy to read or debug, and I’ve been bitten by “optional” arguments enough times to be obsessive about including them in the built-in conditional functions.

Reply | Quote

For subforms with no records, you need to use something other then NZ() (Null to Zero). That’s because a subform/subreport with no records returns an error. So there are two ways to handle it:

=IIF(IsError(NZ( Me!ControlA,0) + NZ( Me!ControlB,0) ,0,NZ( Me!ControlA,0) + NZ( Me!ControlB,0) )

or the method I perfer:

=AvoidError(Me!ControlA + Me!ControlB)

AvoidError being the following function that you paste into a module:

Function AvoidError(n As Variant)

On Error GoTo Trap
AvoidError = n
Exit Function

Trap:
AvoidError = 0
Resume Next
End Function

Note that you can modify AvoidError to check for Null as well if you wish and return a 0.

Jim.

Reply | Quote

Hi Jim,

thanks a lot for your detailed answer. I have to admit, that i got the error message, because there was some data misssing in one table, which should have been there. My fault.
As i just tipped my nose into VB, i will need some time to work through you kindly supplied code to understand it.

Reply | Quote