?
Solved

How to Replace data in Access

Posted on 2011-03-25
6
Medium Priority
?
276 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 1000 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 500 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 28

Accepted Solution

by:
omgang earned 1000 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
Dale Fye earned 500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

743 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