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
8
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
Comment
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

by:als315
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

by:als315
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

by:
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
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

 
LVL 12

Expert Comment

by:pdebaets
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

by:samprg
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

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

Assisted Solution

by:pdebaets
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

by:samprg
ID: 36590601
Awesome
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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…

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.

Join & Ask a Question