Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2011
    Posts
    35

    Unanswered: Update Statment Error Not the proper result

    i have a uniqueZip table which has a set of unique rows with a certain code, now my goal is to update the Route tbl to have the match code by comparing the rows from one table to the other.

    This is my sql statement however this does not change any rows even though there is matching data. Can anyone help me. i attached a image of the tables and fields.


    UPDATE [Route tbl], UniqueZips
    SET [Route tbl].CODE = [UniqueZips]![CODE]
    WHERE ((([Route tbl].CODE)=(StrComp([Route tbl]![WINDOW 1] ,[UniqueZips]![WINDOW 1]) & StrComp([Route tbl]![WINDOW 2] ,[UniqueZips]![WINDOW 2]) & StrComp([Route tbl]![WINDOW 3] ,[UniqueZips]![WINDOW 3]) & StrComp([Route tbl]![WINDOW 4] ,[UniqueZips]![WINDOW 4]) & StrComp([Route tbl]![WINDOW 5] ,[UniqueZips]![WINDOW 5]))));
    Attached Thumbnails Attached Thumbnails my tables.png  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you expect StrComp() to do? The expression StrComp(string1, string2) returns -1 if string1 < string2, 0 if string1 = string2, or 1 if string1 > string2.

    If your purpose is to compare two strings (or string variants), or here the contents of two columns, use the equal ("=") operator, in a WHERE clause or, even better, use an INNER JOIN operation to link both tables.
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    35
    I tried the join below however the fields are not changing.

    UPDATE [Route tbl]
    INNER JOIN UniqueZips
    ON ([Route tbl].[WINDOW 5] = UniqueZips.[WINDOW 5]) AND
    ([Route tbl].[WINDOW 4] = UniqueZips.[WINDOW 4]) AND
    ([Route tbl].[WINDOW 3] = UniqueZips.[WINDOW 3]) AND
    ([Route tbl].[WINDOW 2] = UniqueZips.[WINDOW 2]) AND
    ([Route tbl].[WINDOW 1] = UniqueZips.[WINDOW 1])
    SET [Route tbl].CODE = [UniqueZips]![CODE];

    i have matching data in both tables i pasted the join ad the table data in attachments
    Attached Thumbnails Attached Thumbnails join.png  

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To be sure the join is correct and that the query returns a row set, try with a SELECT query first and see what you get:
    Code:
    SELECT [Route tbl].* 
    FROM [Route tbl] 
    INNER JOIN UniqueZips ON 
    ([Route tbl].[WINDOW 5] = UniqueZips.[WINDOW 5]) AND 
    ([Route tbl].[WINDOW 4] = UniqueZips.[WINDOW 4]) AND 
    ([Route tbl].[WINDOW 3] = UniqueZips.[WINDOW 3]) AND
    ([Route tbl].[WINDOW 2] = UniqueZips.[WINDOW 2]) AND
    ([Route tbl].[WINDOW 1] = UniqueZips.[WINDOW 1])
    Have a nice day!

  5. #5
    Join Date
    Dec 2011
    Posts
    35
    I've tried the select you mentioned i dont get any results back, i dont understand why though because both tables have matching fields can it be because of the nulls ??? I placed a picture in my post before with the data. Thanks.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A picture does not help a lot. Try rebuilding the SELECT query, step by step, adding one condition at a time to the join.
    Have a nice day!

  7. #7
    Join Date
    Dec 2011
    Posts
    35
    Ive been doing that selecting one condition at a time so when i get the first window to match in both tables even though there are only 41 records in the Route tbl i get a result of 189 and then when i add window 3 to the condition i get 5 rows and when windows 4 and 5 come in, i get nothing i dont know what i am doing wrong here ?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try replacing the Null values:
    Code:
    ON 
    (Nz([Route tbl].[WINDOW 5], "") = Nz(UniqueZips.[WINDOW 5], "")) AND 
    (Nz([Route tbl].[WINDOW 4], "") = Nz(UniqueZips.[WINDOW 4], "")) AND 
    (Nz([Route tbl].[WINDOW 3], "") = Nz(UniqueZips.[WINDOW 3], "")) AND 
    (Nz([Route tbl].[WINDOW 2], "") = Nz(UniqueZips.[WINDOW 2], "")) AND 
    (Nz([Route tbl].[WINDOW 1], "") = Nz(UniqueZips.[WINDOW 1], ""))
    Have a nice day!

  9. #9
    Join Date
    Dec 2011
    Posts
    35
    Oh my god thank you so much it was the nulls excellent help my friend, thank you once again! Where did you get that syntax from which reference so i can look into it a bit more myself!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    About JOINS and Null values, see for instance: Null Values and Joins
    Have a nice day!

Posting Permissions

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