Updating master table based on values in other two fields from different table in Access

Question

I am trying to update a master table field called Link_pipe_type from another table. I want to update the Link_pipe_type with if condition, if link 20 is empty the link_pipe_type has to be updated by link_pipe_50 field or else update by link_pipe_20. This is my update query:

UPDATE
    tbl_Draftersreport_upload 
    INNER JOIN tbl_MASTER_List 
        ON tbl_Draftersreport_upload.Ref_ID = tbl_MASTER_List.Ref_ID
    SET
        tbl_MASTER_List.Link_pipe_type = IIF(
            ([tbl_Draftersreport_upload].[Link Pipe 20])="",
            ([tbl_Draftersreport_upload].[Link Pipe 50]),
            ([tbl_Draftersreport_upload].[Link Pipe 20])
);

I am still not able to update the master table with the values. Error:

Microsoft Access didn't update 13 fields due to a type conversion failure due to validation rule violations.

Need help! struggling for 2 days. Thanks in advance

What data type is Link_pipe_type? You allow empty strings in fields (I NEVER do)? Perhaps Link_Pipe_20 field is actually Null, which is not the same as empty string. Why save this value, it can be calculated when needed. — June7

Gauravsa Gauravsa · Accepted Answer · 2018-12-10T04:12:18

you can try this: (check for null)

UPDATE
tbl_Draftersreport_upload 
INNER JOIN tbl_MASTER_List 
    ON tbl_Draftersreport_upload.Ref_ID = tbl_MASTER_List.Ref_ID
SET
    tbl_MASTER_List.Link_pipe_type = IIF(
        IsNull([tbl_Draftersreport_upload].[Link Pipe 20]),
        ([tbl_Draftersreport_upload].[Link Pipe 50]),
        ([tbl_Draftersreport_upload].[Link Pipe 20])
);

A read on Null value and blank value is here: https://www.techrepublic.com/article/learn-the-differences-between-is-null-and-isnull-in-access/

Updating master table based on values in other two fields from different table in Access

1 Answers