Access Query, change data in query?

Hi.
We have an old Access database that we have to run reports from. We only have read access so we cant update the data.
I have been using an IIf statement to make a change to a record that  has been entered in correctly, and this has worked fine for the past 6 months,  Name: IIf([filenumber]="XX1413","Client",[clientname]) So the query looks at the file number, if it matches it then changes that to client, if not it just returns the data that is held in the table clientname.
The issue I have now is that we have to make more changes. For example file number XX1417 needs to be shown as ThirdParty.
Would using Multi Iif statement be best? If so I have tried to nest/ Combine them with no success.

Thanks
George_MiltonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Name: IIf([filenumber]="XX1413","Client",IIf([filenumber]="XX1417","ThirdParty";[clientname]))

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
That would depend on how many more IIfs you intend to have.  Multiple IIfs tend to be more difficult to read.  The Switch function would be the better choice, unless you have quite a bit more reassignments.  If you do have many more, It would be best to add a new table with two fields, Filenumber and AssignedName (for instance).  Then you could join the new table to your query using Filenumber as the join field.

However if you don't need that many more, here's how a Switch function would look like:

Switch([filenumber]="XX1413", "Client", [filenumber]="XX1417", "ThirdParty", ...... [filenumber]="XX####", "anotherName", TRUE, [clientname])

Open in new window


Ron
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The other option is to write a VBA procedure and then call that from the query.  This let's you clearly see and write the IF checks, Select Case's, or whatever approach you use to swap values.

Jim.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dale FyeOwner, Developing Solutions LLCCommented:
I would agree with Jim.  A VBA function would work.

If you only have a couple of cases to handle, the nested iif( ) will work and not be too difficult.

I've had problems with the Switch( ) function in queries in the past.  It works fine in code, but I've experienced situations where the query returns Chinese characters for some fields when I use Switch in a query.  So I generally just avoid that option.

If the number of these changes gets excessive, you could also create a "Client Name Exceptions" table where you list the [FileNumber] and [ClientName] fields and join that to your recordsource with left join.  This would require that you modify your reports recordsource and that field would look something like:

Client: NZ(tbl_ClientNameExceptions.ClientName, yourTable.Client)
IrogSintaCommented:
Interesting Dale, I've never had any problems with the Switch function and I use it all the time.  I wonder if you had something else going on with your system.

Ron
Gustav BrockCIOCommented:
> the query returns Chinese characters for some fields ..

I've seen that too, but not because of the Switch function (it wasn't there) - so you probably shouldn't blame Switch.

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I've had problems with the Switch( ) function in queries in the past.  It works fine in code, but I've experienced situations where the query returns Chinese characters for some fields when I use Switch in a query.  So I generally just avoid that option.>>

 Interesting....I have one client now that routinely was getting DB corruption from a certain app.   Wasn't able to pin down a definitive cause, but the the result was that some fields in the record would be flooded with Chinese characters.

 I'll have to go back and see if that application made use of the switch function in a query.

<<I've seen that too, but not because of the Switch function (it wasn't there) - so you probably shouldn't blame Switch.>>

 There's a definite bug somewhere that was introduced when they went to unicode with database processing.

Jim.
Dale FyeOwner, Developing Solutions LLCCommented:
JIm/Ron,

I was able to definitively peg it down to the Switch( ) function, which I used to use frequently.  Now I prefer to use either a function or the "exception table" method I mentioned above. I believe that the table method is the most sustainable.
Gustav BrockCIOCommented:
> the query returns Chinese characters for some fields

This is a bug typically met if grouping on a memo field. Don't know if this could be the case here.
There may be several workarounds:

    Select
        a, Left(b, 255) As b
    From
        table1
    Group By
        a, Left(b, 255)

    Select
        a, Mid(b, 1) As b
    From
        table1
    Group By
        a, Mid(b, 1)

    Select
        a, First(b) As Someb
    From
        table1
    Group By
        a

    Select
        a, DLookUp("b","table1","Id = " & [table1]![Id] & "") AS b
    From
        table1
    Group By
        a, DLookUp("b","table1","Id = " & [table1]![Id] & "")

/gustav
George_MiltonAuthor Commented:
Thanks for all the info
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.