Null fields and blank fields in MSAccess application

Posted on 2012-09-05
Last Modified: 2012-09-05
I have an Access application.

My customer wants the customer's account number to display on their email if they have an assigned customer account number.  The app works fine if there is an account number.

If  there wasn't a custoomer account number, I got an error. If I change the blank fields to a 0 or to NULL, that prints in the field.  The customer does not want the 0 to print on the emil.

Is there a way to resolve the error and NOT have to print a 0 or NULL when the CustNbr field is blank?   The error I get is  "Error # 94.  Invalid use of Null.

Here is the code:  

Private Sub cmdEmail_Click()
  Dim i As Integer
  Dim varEmail As Variant
  Dim varCustNbr As Variant  
  On Error GoTo Err_cmdEmail_Click
  DQ = """"
  'Debug.Print "whatever"
  For i = 1 To cboINameRecent.ListCount - 1

    varEmail = DLookup("[Email]", "tblEmails", "[Customer Nme] = " & DQ & cboINameRecent.ItemData(i) & DQ)
  cboINameRecent.ItemData(i) & DQ)
    varCustNbr = DLookup("[Cust Nbr]", "tbl_Emails", "[Customer Name] = " & DQ & cboINameRecent.ItemData(i) & DQ)  
    If Not IsNull(varEmail) Then
      strEmail = varEmail
      strINum = cboINameRecent.ItemData(i)
      If IsNull(strCustNbr) Then
            strCustNbr = ""
            strCustNbr= varCustNbr
      End If

    DoCmd.Close acReport, "rptMain"
      cboINameRecent.Value = cboINameRecent.ItemData(i)
      'MsgBox cboINameRecent.ItemData(i) & "/" & cboINameRecent.Value
      DoCmd.SendObject acSendReport, "rptMain", acFormatPDF, strEmail, , , "Customer Recp " & strCustNbr, strEmailMessage, False
      'MsgBox varCustNbr, vbOKOnly, "Operation Complete"

    End If
  Exit Sub
'  MsgBox " Error #: " & Err.Number & vbCrLf & Err.Description, , "cmdEmail_Click"
End Sub
Question by:programmher
    LVL 39

    Accepted Solution

    Your code have error in if:
    If IsNull(strCustNbr) Then
                strCustNbr = ""
                strCustNbr= varCustNbr
          End If

    Open in new window

    should be:

    If IsNull(varCustNbr) Then
                strCustNbr = ""
                strCustNbr= varCustNbr
          End If

    Open in new window

    You can use also:
    strCustNbr = "" & DLookup("[Cust Nbr]", "tbl_Emails", "[Customer Name] = " & DQ & cboINameRecent.ItemData(i) & DQ)  

    Open in new window

    LVL 84
    You can also wrap the DLookup in Nz:

    varCustNbr = Nz(DLookup("[Cust Nbr]", "tbl_Emails", "[Customer Name] = " & DQ & cboINameRecent.ItemData(i) & DQ)  , "")

    That will return either the value in Cust Nbr or an empty string.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now