Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Nz and Dlookup

  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Nz and Dlookup

    Can someone please help me (I've got a mind lapse). I'm having a problem with this statement for a ControlSource on a text box...

    =nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![Provider_No] & "")," ")

    It returns an error on a new blank record and it doesn't want to capitolize the Nz (which makes me wonder if it's right.) Other than that, it does return the proper name if the record exists.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Try
    =nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![Provider_No])," ")

    You do not need & "" after the [Provider_No]

    HTH

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the reply Poppa Smurf but that didn't work. It still doesn't properly case the Nz and I get an error on a new record.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    debug tells me this for tblLix where there is only one IDlix ( = 1 )

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & 1),"NUFFINK")
    DEMO

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & 2),"NUFFINK")
    NUFFINK

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & 1 & " "),"NUFFINK")
    DEMO

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & 2 & " "),"NUFFINK")
    NUFFINK

    so the problem is not either variant of the nz(dlookup()) syntax.

    have a go with
    ? [Forms]![Contracts]![Provider_No]
    immediately after starting the new record

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I have not used NZ but use the IF statement
    If IsNull(DLookup(.....) then ... else .... end if

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There must be something else going on because the syntax seems to be correct...

    =nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![Provider_No] & "")," ")
    or
    =nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![Provider_No])," ")

    which are returning the correct values (even debugging - ?)

    The problem is when I go to a blank record where it gives me a syntax error. For some reason, the nz doesn't seem to be functioning.

    If I do this in the OnCurrent event...

    MsgBox Nz(Forms!Contracts!provider_no, "(No Value)")

    I get (No Value) when I go to a new record so it's recognizing the Nz (and Forms!Contracts!Provider_No). It's just not recognizing the Nz in controlsource of the text box and returns an error.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I can use the IsNull. I just want to use the Nz and it bugs me that it's not working because it should work.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Correction...the IsNull also returns an error when going to a new record so there is something else going on that I can't grab my hands on.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If I do this in the OnCurrent event (Test1 is unbound)...

    Me!Test1 = Nz(DLookup("[Status]", "contract_status", "[status_code] = " & [Forms]![Contracts]![status_code]), "No Value")

    I get a syntax error ('[status_code] = ') when I go to a blank record.

    None of the Nz are working for the textbox controls. It doesn't like something with the where part of the dlookup.
    Last edited by pkstormy; 06-25-07 at 13:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here are some variations on the theme:


    ? nz(dlookup("LixSerial", "tblLix", "IDlix = NULL" ),"NUFFINK")
    NUFFINK

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & NULL & " "),"NUFFINK")
    =>runtime 3075: syntax error

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = "),"NUFFINK")
    =>runtime 3075: syntax error

    ? nz(dlookup("LixSerial", "tblLix", "idBAD = " & 1 & " "),"NUFFINK")
    =>runtime 2001: you cancelled the previous operation

    ? nz(dlookup("LixBAD", "tblLix", "IDlix = " & 1 & " "),"NUFFINK")
    =>runtime 2001: you cancelled the previous operation

    ? nz(dlookup("LixSerial", "tblBAD", "IDlix = " & 1 & " "),"NUFFINK")
    =>runtime 3078: cannot find tblbad

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & DoesNotExist ),"NUFFINK")
    =>runtime 3075: syntax error

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & Forms!NoSuchForm!NoSuchControl ),"NUFFINK")
    =>runtime 2450: cant find the form

    ...and here's a totally unexpected result:

    ? nz(dlookup("LixSerial", "tblLix", "IDlix = " & vbnull ),"NUFFINK")
    DEMO

    go on Paul, tell us which error# you get.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's the scenario...

    I have a table called Contracts which has a Provider_No long integer field type (Number).

    I have a form also called Contracts with a Contracts table recordsource (I tried renaming the form and dlookup syntax in case it somehow conflicted with the table name but that wasn't it.)

    On the Contracts form, there is a combobox called: Provider_No and Status_Code (I double-checked the names on the comboboxes - even copied/pasted the names for the dlookup).

    I have 2 textbox controls with syntax like the following in the controlsource of each...
    =Nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![provider_no] & ""),"<Enter Provider Number> ")
    and
    =nz(DLookUp("[Status]","[contract_status]","[status_code] = " & [Forms]![Contracts]![status_code] & ""),"No Status Code")

    Everything works ok with the dlookups as long as there is a value in the Provider_No and the Status_Code comboboxes. When I go to a blank record, the Nz fails and I get an #Error# in the boxes.

    If I make the textboxes unbound and put some code in the OnCurrent event such as...
    me!TextProviderName = Nz(DLookUp("[Provider_Name]","[Providers]","[Provider_No] = " & [Forms]![Contracts]![provider_no] & ""),"<Enter Provider Number> ")
    and
    me!StatusName =nz(DLookUp("[Status]","[contract_status]","[status_code] = " & [Forms]![Contracts]![status_code] & ""),"No Status Code")

    it works ok until I go to a new record where I get this...

    Syntax error (missing operator) in query expression '[Provider_No] = '

    and
    Syntax error (missing operator) in query expression '[status_code] ='

    I can't put my hands on this but the syntax seems to be correct. I took the default Lookup out of the fields in the table for the Provider_No and Status fields thinking that might be it but that didn't work.

    I didn't design the form but I removed all code behind the scenes just to make sure. I also changed the tab order so Contract_ID (the primary key (autonumber) is the first tab). There also is no default value for Provider_No and Status.

    There's something I'm missing.
    Last edited by pkstormy; 06-25-07 at 14:34.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If I do this behind the scenes in the OnCurrent event it works ok...
    If Not IsNull(Me!status_code) Then
    Me!statusName = Nz(DLookup("[Status]", "[contract_status]", "[status_code] = " & [Forms]![Contracts]![status_code] & ""), "No Status Code")
    Else
    Me!statusName = "Need Value"
    End If

    and "Need Value" is put in the unbound StatusName textbox.

    But I should be able to do this with the Nz function and not have to do the isnull test. Is there any reference for the Nz function that maybe I don't have? I have the same references I've been using all the time without problems.
    Last edited by pkstormy; 06-25-07 at 14:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think the problem is that on a new record, the value to be looked up is null, so the DLookup returns an error, not Null. That's why the nz() doesn't help. Try:

    =IIf(IsNull([status_code]),"No Status Code",DLookUp("Status","contract_status","status_c ode=" & [status_code]))

    Edit: I don't have a space in there, the site added it.
    Paul

  14. #14
    Join Date
    May 2007
    Posts
    38
    paul

    have you sorted this yet?

    surely if its a new record then everything in the record is nothing (probably not the right technical expression - or maybe it is) as opposed to null, and therefore your test will probably return #error. Is that the cause by any chance?

    just as auotnumber shows [autonumber] until the insert record invents, and it then gets populated.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    My references are...
    Visual Basic for Applications
    Microsoft Access 11.0 Object Library
    Microsoft DAO 3.6 Object Library
    OLE Automation
    Microsoft Visual Basic For Applications Extensibility 5.3
    Microsoft ActiveX Data Objects 2.1 Library
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.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
  •