Solved

How to Replace data in Access

Posted on 2011-03-25
6
271 Views
Last Modified: 2012-05-11
I am trying to replace data in a particular field via Query.  I'm currently using the IIF([field] ="current data", "new data".  This works fine except for the fact that I have 30 different occurances of data in that particular field.  Query will not let me use that many IIF statements in a single query.  That wouldn't be a problem, I could run two queries except for the fact that the IIF statement blanks out the field if none of the occurances are matched.

  Is there a way using IIF to leave the existing data intact if it does not match any of the IIF statements?  If not, what's a better way to do this.

Here's a portion of the IIF statement I'm using:

SELECT IIf([Failure Code]="UM","Unscheduled Maintenance",IIf([Failure Code]="AL","Alignment",IIf([Failure Code]="CC",IIf([Failure Code]="CI","Customer Induced",IIf([Failure Code]="DE","Design",IIf([Failure Code]="DM","Deferred Maintenance",IIf([Failure Code]="GM","General Maintenance",IIf([Failure Code]="HA","Handling"

0
Comment
Question by:ckuder
6 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 250 total points
ID: 35218126
The last Else part should be [Failure Code], e.g.  IIf([Failure Code]="UM","Unscheduled Maintenance", [Failure Code])
This way if none of the IIf parts match the field value will remain as it started.

OM Gang
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 35218134
use this format

iif([fieldName]="GM","General Maintenance",[FieldName])

if the values are not find, use the value of the field
0
 
LVL 28

Expert Comment

by:omgang
ID: 35218141
You could also use a VBA routine to replace the values.
OM Gang
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 28

Accepted Solution

by:
omgang earned 250 total points
ID: 35218189



PUblic Function ReplaceValue()

    Dim rs As DAO.Recordset
    Dim strNewVal As String

    Set rs = CurrentDb.OpenRecordset("MyTableName")
    rs.MoveFirst
    Do Until rs.EOF
        Select Case rs([Failure Code])
            Case "UM"
                strNewVal = "Unscheduled Maintenance"

            Case "AL"
                strNewVal = "Alignment"

            ....
            ....

            Case Esle
                strNewVal = rs([Failure Code])

        End Select


        rs.Edit
            rs([Failure Code]) = strNewVal
        rs.Update
        rs.MoveNext
    Loop

    Set rs = Nothing
End Function

OM Gang
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 35218261
Use the Switch() function.  This accepts an array of pairs of information,  the first element is must evaluate to a logical true/false, the second element is the value to use if the first is encountered.

SELECT  Switch([Failure Code]="UM","Unscheduled Maintenance", _
                         [Failure Code]="AL","Alignment", _
                         [Failure Code]="CC", "you forgot to enter a value for this", _
                         [Failure Code]="CI","Customer Induced", _
                         [Failure Code]="DE","Design", _
                         [Failure Code]="DM","Deferred Maintenance", _
                         [Failure Code]="GM","General Maintenance", _
                         [Failure Code]="HA","Handling", _
                         True, [Failure Code]) as [Failure Desc]
FROM your Table


0
 

Author Closing Comment

by:ckuder
ID: 35218375
Thank you for all the suggestions. You have all been a tremendous help.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

786 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