How do i replace a null record with a string in Microsoft Acces

Posted on 2011-05-04
Last Modified: 2013-12-25
I have a very large table (table#1) with over 130,000 records.  i have another table that is empty with just many field names and all with empty records(table#2) which is really a template that cannot change its field name order.  I need to update some of the fields in the table2 template with records from fields in table#1.  Other fields i will need to populate with a specific string.


Thank you!  
Question by:Zedway
    LVL 74

    Accepted Solution

    Can you post a *specific* example please?

    Nulls are often associated with Numeric fields.
    So numeric fields cannot contain strings (text)

    In a nutshell you can replace all nulls in a field with an update query similar to this:

    UPDATE YourTable SET YourTable.YourNullField = "SomeString"
    WHERE YourTable.YourNullField Is Null;

    But again, we need a specific example to be sure...


    LVL 23

    Assisted Solution

    1.  "I need to update some of the fields in the table2 template with records from fields in table#1"
    - assuming relationship column between table1 and table2 is col1

    UPDATE table2 a SET a.col2 = (SELECT b.col2 FROM table1 b WHERE b.col1 = a.col1)

    Open in new window

    2. "Other fields i will need to populate with a specific string."
    - assuming you have run the update in #1 above and column in table2 left with only those records null and no relation/data exist in table1

    UPDATE table2 a SET a.col2 = 'anystring' WHERE a.col2 IS null

    Open in new window

    LVL 44

    Expert Comment

    Are you saying you need to change the name of some of the fields in table2 based on names in table1?  If you start adding data to table2, it will not longer be empty - just a template.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now