Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Question Unanswered: 'Invalid use of Null' please help

    Invalid use of Null. Please help
    Can sombody tell me where I am doing wrong in the following code.

    Private Sub viewresults2(rptPrint As String)

    Dim stWhere As String
    Dim myInt As String
    Dim myInd As String
    myInd = CbCustomer
    Select Case Me!frmRelativequeries1
    Case 1
    myInt = Forms![New form Sample]!Cbpartno
    stWhere = "[CUSTOMER] = '" & myInd & "' And [PART NO] = '" & myInt & "'"
    Case 2
    myInt = Forms![New form Sample]!Cbrejcode
    stWhere = "[CUSTOMER] = '" & myInd & "' And [REJ CODE] = '" & myInt & "'"
    Case 3
    myInt = Forms![New form Sample]!Cbliabcat
    stWhere = "[CUSTOMER] = '" & myInd & "' And [CAT] = '" & myInt & "'"
    Case 4
    myInt = Forms![New form Sample]!Cbcustomerdefcode
    stWhere = "[CUSTOMER] = '" & myInd & "' And [DEFECT CODE/COMPLAINT CODE] = '" & myInt & "'"
    Case Else
    stWhere = ""
    End Select
    DoCmd.OpenForm (rptPrint), acNormal, , stWhere
    End Sub

    If the customer combobox is left blank. It prompts as

    'Invalid use of Null'. It works fine if I put some data in customer combobox.

    Thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Cool

    A variable cannot be compared to be "equal to NULL," i.e. "[xyz] = NULL."

    The comparison must be IS NULL.

    Easiest thing to do with your code is to put an if-statement ahead of what you've got, like this:
    Code:
      if IsNull(myInt) then
        s = "IS NULL"
      else
        s = "= " & myInt
      endif
    Use this in constructing your stWhere strings.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    Thank you for your reply.

    This much be the solution by as I am not very good in VB codes so I am confused a bit because I do not know how to use this code.

    Should I put this if statement in case statements after srtWhere string or somewhere in the beginning.

    Looking forward to your reply.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    at the top of your sub....

    Private Sub viewresults2(rptPrint As String)
    if isnull(Forms![New form Sample]!Cbcustomerdefcode) then
    msgbox "Choose a customer!", vbinformation, "Operator Error!"
    exit sub
    endif
    ...and carry on with your sub as before


    izy

  5. #5
    Join Date
    Oct 2003
    Posts
    66
    How about using the NZ function....
    Look it up in the help...it convers a null value to anything you want so that it can be used.

    Example

    Var1 = Null

    var2 = NZ(var1,"")

    Var2 will equal ""

    Basically in your code where you have myInd put this

    NZ(myInd,"")

    should solve problem i think

  6. #6
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Thumbs up

    Thanks to all who replied.

    I have tried izy's suggestion and it is all working fine now.

    Thanks a lot for your help.

Posting Permissions

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