Left Join SQL Access change blank field

Posted on 2007-07-23
Last Modified: 2012-06-27
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

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

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?

Question by:vishalarya
    LVL 15

    Expert Comment

    select row1, iif(isnull(row2), 'N/A', row2)
    LVL 29

    Expert Comment

    Use the IIF conditional

    IIF(your_field = "", "N/A", your_field)
    LVL 119

    Accepted Solution

    UPDATE T1 LEFT JOIN T2 ON T1.row1 = T2.row1 SET T1.row2 = IIf(IsNull([T2].[row1]),"n/a",[t2].[row2]);
    LVL 29

    Expert Comment

    Oh, you're welcome.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now