• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 888
  • Last Modified:

Left Join SQL Access change blank field

When using an update query in Access, I'm doing a left join on two tables, so that those rows that match will be updated with fields from the 2nd table, while if the rows in the 1st table don't match, then the field will be blank.  However, I want this field, if blank, to default to something else, for example "N/A".  I outline a simple example:

Table 1:                        Table2:
---------
row1  row2               row1           row2
1                                1                a
2                                2                b
3

If I LEFT JOIN Table 1 and Table 2 on row1, and set table1.row2 = table2.row2, I will get:

Table1:
row1   row2
1         a
2         b
3        ""

I would like the 3rd position to not be blank, but some other text.  How can I do this?

Thanks,
Vishalarya
0
vishalarya
Asked:
vishalarya
  • 2
1 Solution
 
derekkrommCommented:
select row1, iif(isnull(row2), 'N/A', row2)
0
 
BadotzCommented:
Use the IIF conditional

IIF(your_field = "", "N/A", your_field)
0
 
Rey Obrero (Capricorn1)Commented:
UPDATE T1 LEFT JOIN T2 ON T1.row1 = T2.row1 SET T1.row2 = IIf(IsNull([T2].[row1]),"n/a",[t2].[row2]);
0
 
BadotzCommented:
Oh, you're welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now