Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Query

Posted on 2011-09-22
Medium Priority
229 Views
Last Modified: 2012-06-22
Hi,

I have quer in sql and I want to convert it in MS access

update  YourTable
Set   ColumnResult  =case when SUBSTRING(RIGHT(column1,7),1,2)='ws' then 'JK'
when SUBSTRING(RIGHT(column1,7),1,2)='sa' then 'KR'
end
0
Question by:samprg
[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
• 3
• 3
• 2
8 Comments

LVL 40

Expert Comment

ID: 36585035
IIF(mid(Right(column1,7),1,2)="ws", "JK", IIF(mid(Right(column1,7),1,2)="sa","KR",""))
0

LVL 40

Expert Comment

ID: 36585054
Of full query (for Table1):
UPDATE Table1 SET Table1.ColumnResult = IIf(Mid(Right([column1],7),1,2)="ws","JK",IIf(Mid(Right([column1],7),1,2)="sa","KR",""));
0

LVL 40

Accepted Solution

als315 earned 1000 total points
ID: 36585056
For YourTable:
UPDATE YourTable SET YourTable.ColumnResult = IIf(Mid(Right([column1],7),1,2)="ws","JK",IIf(Mid(Right([column1],7),1,2)="sa","KR",""));
0

LVL 12

Expert Comment

ID: 36585065
update  YourTable Set   ColumnResult  = iif(Instr(1,RIGHT(column1,7),2)='ws,'JK',iif(Instr(1,RIGHT(column1,7),2)='sa','KR',""))
Where Instr(1,RIGHT(column1,7),2)='ws OR Instr(1,RIGHT(column1,7),2)='sa';
0

Author Comment

ID: 36585075
How can I use
IIF(mid(Right(column1,7),1,2)="ws", "JK", IIF(mid(Right(column1,7),1,2)="sa","KR",""))
in  update statement
0

Author Comment

ID: 36585085
I am sorry, I did not see your answers I should refresh IE.
I will try it
0

LVL 12

Assisted Solution

pdebaets earned 1000 total points
ID: 36585094
My use of "Instr" is incorrect above. Please use

update  YourTable Set   ColumnResult  = iif(Mid(RIGHT(column1,7),1,2)='ws,'JK',iif(Mid(RIGHT(column1,7),1,2)='sa','KR',""))
Where Mid(RIGHT(column1,7),1,2)='ws OR Mid(RIGHT(column1,7),1,2)='sa';
0

Author Closing Comment

ID: 36590601
Awesome
0

## Featured Post

Question has a verified solution.

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

Preparing an email is something we should all take special care with â€“ especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challenâ€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
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â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll

#### 596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.