Access Query, change data in query?

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)

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

> 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

<<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.

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.

> 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

Thanks for all the info