Solved

How to Replace data in Access

Posted on 2011-03-25
6
269 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now