Change result if condition is true

[rfigueroa1976]

Hello again, all....



I think I might have an easy one.... Is there a way/command to change the result of a query result if a condition is true? For example, if I was querying all employee names and wanted to change the result of "Mike" to "Michael" but have all other stay exactly the way they are.

Desired Rule: list all employee names; however, if name is Mike, change to Michael.

Below is the SQL view of my access query (Simplified/Trimmed):

SELECT dbo_orderheader.ord_number AS [Order Number], dbo_orderheader.ord_refnum AS [Reference Number], dbo_orderheader.ord_origin_latestdate AS [Scheduled Pickup Date], dbo_orderhearder.ord_EmpName AS [Representative]

The rule I'm assuming would go here....


FROM (((((((((((dbo_NCR_Occurrence INNER JOIN dbo_orderheader ON dbo_NCR_Occurrence.OrderNumber = dbo_orderheader.ord_number)
GROUP BY dbo_orderheader.ord_number, dbo_orderheader.ord_refnum, dbo_orderheader.ord_origin_latestdate, MonthName(Month([dbo_orderheader].[ord_origin_latestdate])),
HAVING (((dbo_orderheader.ord_origin_latestdate) Between [start] And [end]) AND ((dbo_city.cty_name) Like "louisville" Or (dbo_city.cty_name) Like "fayetteville" Or (dbo_city.cty_name) Like "washington" Or (dbo_city.cty_name) Like "belle" Or (dbo_city.cty_name) Like "new johnsonville" Or (dbo_city.cty_name) Like "gregory" Or (dbo_city.cty_name) Like "deepwater" Or (dbo_city.cty_name) Like "parkersburg" Or (dbo_city.cty_name) Like "carlin") AND ((dbo_NCR_RootCauses.Description) Not Like "Internal*") AND ((dbo_NCR_Occurrence.BilltoName) Like "Chemours*") AND ((dbo_NCR_Occurrence.Status_ID) Not Like "26"))
ORDER BY dbo_orderheader.ord_origin_latestdate, dbo_city.cty_name, dbo_NCR_RootCauses.Description;

[Beetle]

Why not just use an update query to fix the names at the table level?

UPDATE dbo_orderheader set ord_EmpName = "Michael" WHERE ord_EmpName = "Mike";

If you're going to do this make a backup first.

If for some reason you can't (or don't want to ) do that, you can use something like the following at the query level;

Iif(dbo_orderhearder.ord_EmpName = "Mike", "Michael", dbo_orderheader.ord_EmpName) AS [Representative]

[rfigueroa1976]

Thanks for the reply but, i'm having a problem with that. Before adding that, my query came up with prompt boxes requesting a date range (Date from, then date to) as i designed. Once I add that command, another/additional prompt box comes up for that name field (Enter Parameter Value: dbo_orderheader.ord_EmpName). Do you know what causes that?

If i fill that prompt box with anything, all of the results change to what i entered.

[Beetle]

Check the spelling of the table name. If you just did a copy/paste from what I posted I may have spelled it incorrectly. Looks like the table name is dbo_orderhearder, not dbo_orderheader.

[rfigueroa1976]

YUP! It was an error on my part. THANK YOU!

Now.... one last thing Is there a reason why my wild card wouldn't work with this? When I type in the full name, it does find and convert but when I use the wild card, it does nothing.

Works: IIf([dbo_orderhearder.ord].[EmpName]="Mike - Johnson","Michael - Johnson",[dbo_orderhearder.ord].[EmpName])

Does not work: IIf([dbo_orderhearder.ord].[EmpName]="Mike*","Michael - Johnson",[dbo_orderhearder.ord].[EmpName])

[Beetle]

If you're going to use a wildcard then you have to use the Like operator, not the = operator;

IIf([dbo_orderhearder.ord].[EmpName] Like "Mike*","Michael - Johnson",[dbo_orderhearder.ord].[EmpName])

However, the above would convert any name beginning with Mike to Michael - Johnson. Is that what you want?

[rfigueroa1976]

YOU ROCK!

Yes I'm aware of all changing to Michael. I'm using "Mike" and "Michael" for this scenario to simplify the explanation. It's actually on some internal jargon for work that would have taken too long to explain. You have helped a bunch! Thanks!

[rfigueroa1976]

Request for a modification to this request. I'm trying to do the same thing but change depending on the result.... for example,

If the name is "Mike", change to "Michael". If the name is "Chuck", change to "Charles"

I tried the below but it didn't work. Please let me know how to set this up. Thanks.

IIf(or([dbo_orderhearder.ord].[EmpName] Like "Mike*","Michael - Johnson",[dbo_orderhearder.ord].[EmpName]), [dbo_orderhearder.ord].[EmpName] Like "Chuck*","Charles",[dbo_orderhearder.ord].[EmpName])

[June7]

Or is not a function in Access it is in Excel.

Syntax:
IIf(something Like this1 OR something Like this2, do this, else do this)

Nested - you need this:
IIf(something Like this1, do this, IIf(something Like this2, do this, else do this for all others))

Explore Switch as alternative to nesting:
Switch(something Like this1, do this, something Like this2, do this, True, do this for all others)

[rfigueroa1976]

Awesome! THANK YOU!!!!