none
Left Join Formula Error RRS feed

  • General discussion

  • Thanks for taking the time to read my question.

    I have a LEFT JOIN query where I have a calculated field. The Join is NOT on the calculated field.

    My calculated field is easy: Value1 - Value2

    Value1 comes from the left table, Value2 comes from the right table. My formula returns #Error when there is no record for Value2 (the record in the left table does not have a match in the right table).

    I have VBA where I'm doing a TransferSpreadsheet of my query results and when this runs I get a message saying unable to append all the data to the table: The contents of fields in 192 record(s) were deleted.... which is my case because I have a 192 records with no matches between the left and right tables and so I'm getting 192 #Error results.

    I've tried making a function that tries to detect when the Value2 is Null, = "", but it seems the record is not even passed to my Function. I've made Value2 Optional, I've made Value1 and Value2 String instead of numbers.... out of ideas on how to get around this message as I need all values to be moved.

    If I open the query and click on the External Data tab and export to Excel, it exports all the data with no warning, and no #Error either.

    How do I get rid of the #Error in my query?

    Thanks,
    Brad

    Monday, September 28, 2015 4:00 PM

All replies

  • ...of course I find the answer AFTER I post!!!

    I put NZ() around my right table value and all is well. The record is passed and returns a zero as I need.

    Thanks,

    Brad

    Monday, September 28, 2015 4:07 PM
  • Is either the left table or the right table a linked Excel sheet?
    Monday, September 28, 2015 4:27 PM
  • Hi Transistor1,

    No, there are no linked tables or files to my DB. The source tables are in the db (not split).

    Using NZ() is the solution that worked for me.

    Thanks for your reply.

    Brad

    Monday, September 28, 2015 4:29 PM
  • My calculated field is easy: Value1 - Value2

    Hi Brad,

    You could convert the Null value to 0 with the Nz-function:

        result = Value1 - Nz(Value2,0)

    See the Help for more information on Nz.

    Imb.

    Edit:

    I see now that you already found this solution.

    This "stupid" forum often refuses to work in the right way, as it does not show all the replies, even not after a refresh, and another refresh ...

    Imb.

    • Edited by Imb-hb Monday, September 28, 2015 4:48 PM
    Monday, September 28, 2015 4:42 PM
  • Just to add one thing here... mbrad, your post is classified as a discussion, not a question.  It's not a big deal, but you might get a better response if your posts are classified accordingly.
    Monday, September 28, 2015 6:16 PM
  • Hi RunningMan,

    I can see THAT you answered, but I cannot see WHAT you answered!

    Why this is not functioning properly?

    Imb.

    Monday, September 28, 2015 8:34 PM