Solved

How to Replace data in Access

Posted on 2011-03-25
6
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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!

 
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 48

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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