[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Null fields and blank fields in MSAccess application

Posted on 2012-09-05
Medium Priority
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 40

Accepted Solution

als315 earned 800 total points
ID: 38369128
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 85
ID: 38369178
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

834 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