Solved

Query

Posted on 2011-09-22
8
178 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
IIF(mid(Right(column1,7),1,2)="ws", "JK", IIF(mid(Right(column1,7),1,2)="sa","KR",""))
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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 39

Accepted Solution

by:
als315 earned 250 total points
Comment Utility
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

by:pdebaets
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:samprg
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
Awesome
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now