Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44

    Question Unanswered: DLookUp #Error Question

    Set Up Info:
    I have a table called [tbl_Load] that contains fields [RC1_Name] and [RC2_Name]. These 2 fields contain numbers. They reference another table named [tbl_RCInfo] and match the primary key there named [RC_ID] which is also a number and there is a text field there named [RC_Number].

    What happens is basically a loader loads a trailer from either 1 or 2 railcars. The RC_Name fields record the railcars that were used. The issue is that only about every 4th or 5th load a 2nd railcar used. So the field [RC2_Name] is not populated. When I run the query, most of the RC2_Name field are blank, but some have #Error in them.

    What I want to happen via a query:

    I would like the query to return the [RC_Number] based upon the [RC_ID] (which is taken from [RC1_Name] and [RC2_Name]). If there is nothing in the field, I would like it remain blank or show "N/A" in the query.

    This is some of what I have tried via the query:

    1) I tried using DLookUp:
    Code:
    DLookUp("[RC_Number]","tbl_RCInfo","RC_ID= " & Nz([tbl_Load.RC2_Name],0)) AS RC2
    ..I used Nz because I am getting #Error ony some of the loads where the 2nd railcar is not present.

    2) I tried using IIF with DLookUp:
    Code:
     IIF(Nz(DLookup("RC_Number","tbl_RCInfo","RC_ID="&[RC2_Name]),0)) AS RC2
    ...Again with the #Error which only happens on some of the records. Most of the records show blank on the query, but some show #Error.

    Am I using DLookUp incorrectly to replace a Null with a 0?

    Any help would be appreciated. I have been trying to get this to work for 2 days from reading hundreds of posts and using the Access help system.
    "Things are only impossible until they are not." ~Jean Luc Picard

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When you say this:
    When I run the query, most of the RC2_Name field are blank, but some have #Error in them.
    Do you mean tbl_Load.RC2_Name has an "#Error" value, or that RC2 (the derived field) as an #Error value?

    If it's the former, you have garbage data. If it's the latter, then I'm curious as to the value of tbl_Load_RC2_Name for the records in question.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Thanks for the reply Teddy.

    The derived fields are the ones showing #Error. The value of the the fields in tbl_Load.RC2_Name are blank as no 2nd railcar was used for loading. But I do find it interesting that only some of them show #Error, while the majority are just blank.

    This is why I was thinking that maybe I was using DLookUp and Nz incorrectly.
    "Things are only impossible until they are not." ~Jean Luc Picard

  4. #4
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Here is some more info.

    I created the same query in a backup of this database that I have since turned into a testing database. The query worked great, so I copied into the live DB. I am still getting the #Error for RC2, but not RC1 even though the query runs great in the test DB.

    I looked at the table and the fields that are showing #Error are blank (Null) and the fields that are working properly also are blank. I ran compact and repair, but that did not fix the issue.
    "Things are only impossible until they are not." ~Jean Luc Picard

  5. #5
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    OK, I have fixed the issue.

    In the test DB I changed the fields of RC1_Name and RC2_Name to Number so to match the PK in tbl_RCInfo.RC_ID. I apparently forgot to do this in the live DB. The query now works as intended.

    Thanks.
    "Things are only impossible until they are not." ~Jean Luc Picard

Posting Permissions

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