Solved

How to Replace data in Access

Posted on 2011-03-25
6
273 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

726 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