Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Search and replace fields in Access table

Posted on 2012-08-15
9
Medium Priority
?
478 Views
Last Modified: 2012-08-15
I have a table with about 50 fields.

I would like an easy way to run a macro or procedure to update all fields to "null" if the value of the entire field is 0.

I don't want to list each field.  I just want it to look at all fields, and if any have a "0", then change it to null.

Is there a simple way to automatically do this?

Thanks!
0
Comment
Question by:Patty01Access
  • 5
  • 3
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38298264
Try using a recordset

Dim rs as object
dim fld as object
set rs = currentdb.OpenRecordset ("YourTable")

Do until rs.eof
for each fld in rs.fields
    if fld.value = 0 then
        fld.value = Null
     end if
next
loop

Open in new window

0
 
LVL 22

Expert Comment

by:Flyster
ID: 38298304
You can highlight your table and then use the replace function under the Home tab.

Flyster
0
 

Author Comment

by:Patty01Access
ID: 38298320
Thanks!

But I'm getting an error.

First, I changed all the "rs" to "rs2" since I'm running a procedure above this one that uses "rs".  Here's how I changed it:
Dim rs2 As Object
Dim fld As Object
Set rs2 = CurrentDb.OpenRecordset("tblVacyTempUnits")

Do Until rs2.EOF
For Each fld In rs2.Fields
    If fld.Value = 0 Then
        fld.Value = Null
     End If
Next
Loop

But on the
        fld.Value = Null
I get the error "Update or CancelUpdate without AddNew or Edit"

What do I need to change?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:Patty01Access
ID: 38298325
Thanks Flyster, but I want to automate it with some kind of code or something.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38298338
Sorry - you need an Edit and an update:


Dim rs2 as object
dim fld as object
set rs2 = currentdb.OpenRecordset ("YourTable")

Do until rs2.eof
for each fld in rs2.fields
    if fld.value = 0 then
         rs2.Edit
        fld.value = Null
         rs2.update 
     end if
next
loop

Open in new window

0
 

Author Comment

by:Patty01Access
ID: 38298370
I changed it, but now it just "hangs up".  I have 1720 records in the table, about 50 fields.

It runs for about 5 minutes -- I keep seeing "not responding" at the top.

Then I hit the escape key to stop it.

I have a pretty decent computer running Windows 7 and Access 2010.  And I have a bunch of big tables and it runs stuff on those really fast, like a few seconds.

Is there something else I could put to make it execute in about 2 seconds?  That's how long Excel takes, so maybe I should just be doing it there?
0
 

Author Comment

by:Patty01Access
ID: 38298392
Here's the table and the code in the attached if it's of any help.
Database1.accdb
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 2000 total points
ID: 38298503
Yikes.

Place the following line between the Next and Loop statements.

Rs2.movenext
0
 

Author Closing Comment

by:Patty01Access
ID: 38298545
Perfecto!

Thanks much mbizup!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

581 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