Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Location
    Hamburg
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi ,
    i am very new to Access, do not understand VB and would like to ask for your help. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    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!Gesam tkosten - Forms!frmBilanz!frmBSfrmfunBankanKasse!BankanKasse + Forms!frmBilanz!frmBSfrmfunForderungen!Gesamtpreis + Forms!frmBilanz!frmBSfrmfunDarlehen!Darlehen
    Else
    Forms!frmBilanz!ErgebnisTextfeld = Forms!frmBilanz!frmBSfrmBankKasse!BAnfangsstand - Forms!frmBilanz!frmBSfrmfunVerbindlichkeiten!Gesam tkosten - 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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    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.....
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Location
    Hamburg
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Ops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I knew that when i finally dare to post a question here, i would put shame on me. <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    But thank you very much for your help! <img src=/S/bow.gif border=0 alt=bow width=15 height=15> 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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    You left out the substitution, Mark.

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

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

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

    Well, if Eileen could integrate the VBA editor into the messaging, I wouldn't make these mistakes! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Jim.

  8. #8
    New Lounger
    Join Date
    Jun 2001
    Location
    Hamburg
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <img src=/S/read.gif border=0 alt=read width=19 height=33>

  9. #9
    New Lounger
    Join Date
    Jun 2001
    Location
    Hamburg
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •