Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Answered: Help with Nz function

    Hello,

    I inherited an application and am not sure why this isn't working. The application was a *.adp and I've been tasked with upgrading it to work with Access 2016. The Nz function doesn't seem to be working. I've tried with, and without the valueifnull. The thing I don't understand, is it keeps returning "Invalid use of Null". Here is the code.

    strCategory = Nz(DLookup([Category], "tblCharges", "[ServiceArea]=" & strServiceArea & _
    " AND [ServiceCode]=" & strServiceCode & " AND [ChargeCode]=" & strChargeCode), 0)

    All variables have valid values, and [Category] is Null. Why doesn't it get assigned the value of 0? I've tried "0" and " " also. The syntax looks right to me. I'm lost. This code works in the adp version of the application, but not in the accdb version. The tables are linked tables to an SQL server.

    Thanks!

  2. Best Answer
    Posted by padraig

    "Figured it out myself. Here is the final code that works in Access 2016. I needed to add "'" around the variables.

    strCategory = Nz(DLookup("[Category]", "tblCharges", "[ServiceArea]='" & strServiceArea & _
    "' AND [ServiceCode]='" & strServiceCode & "' AND [ChargeCode]='" & strChargeCode & "'"), 0)"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 34
    can strServiceArea , strServiceCode or strChargeCode be null?
    this will cause error.

    try
    vRetval =DLookup([Category], "tblCharges", "[ServiceArea]=" & strServiceArea & " AND [ServiceCode]=" & strServiceCode & " AND [ChargeCode]=" & strChargeCode),
    vRetval = nz(vRetval)

    but all params cannot be null

  4. #3
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    No, at least in my testing using the immediate window, all the variables have a value. I just tried this code as an alternative. And it seems like it may not be the Nz function that is returning the "Invalid use of Null" error. It seems to be coming from the DLookup function. Because this code gives me the same error, even though I'm trying to evaluate it for Null values, it just stops before it's even evaluated.

    ' If IsNull(DLookup([Category], "tblCharges", "[ServiceArea]=" & strServiceArea & _
    ' " AND [ServiceCode]=" & strServiceCode & " AND [ChargeCode]=" & strChargeCode)) Then
    ' strCategory = 0
    ' Else
    ' strCategory = DLookup([Category], "tblCharges", "[ServiceArea]=" & strServiceArea & _
    ' " AND [ServiceCode]=" & strServiceCode & " AND [ChargeCode]=" & strChargeCode)
    ' End If

  5. #4
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Figured it out myself. Here is the final code that works in Access 2016. I needed to add "'" around the variables.

    strCategory = Nz(DLookup("[Category]", "tblCharges", "[ServiceArea]='" & strServiceArea & _
    "' AND [ServiceCode]='" & strServiceCode & "' AND [ChargeCode]='" & strChargeCode & "'"), 0)

Posting Permissions

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