[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-05-04
Medium Priority
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

Jeffrey Coachman earned 1000 total points
ID: 35695221
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

OP_Zaharin earned 1000 total points
ID: 35695631
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

ID: 35701354
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.  

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

834 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