By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Changing character data

P: 1
I have a problem where I need to change the first character of the data into other character.
eg: S-1234 to W-1234. Use this code
Expand|Select|Wrap|Line Numbers
  1. SELECT "W" & Mid([ITEMNO],2,24) AS ERPCODE
and the result is flawless. But the problem is, I have 3 names and I just want to replace 2 of them, the other one I want the name just like original. Any idea to solve my problem?
Aug 17 '17 #1

✓ answered by NeoPa

It's not clear what criteria you have for determining which records to change and which to leave alone. This is particularly unfortunate as that's exactly the most important piece of information for the question to make sense.

Luuk's solution will give you two full sets of records - one with changed values and the other with the originals. Not what you desire if I understand your question correctly.

I can't include your criteria, of course, but I can show you what to fit your criteria into that will return what you request :
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf({YourCriteriaHere},'W' & Mid([ITEMNO], 2,24),[ITEMNO]) AS [ERPCODE]

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,034
IT's not really clear what you do mean...

Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT "W" & Mid([ITEMNO],2,24) AS ERPCODE From Table
  2. UNION ALL
  3. SELECT "S" & Mid([ITEMNO],2,24) AS ERPCODE From Table
  4.  
This should give the correct codes unless they both exist.
Aug 19 '17 #2

NeoPa
Expert Mod 15k+
P: 31,418
It's not clear what criteria you have for determining which records to change and which to leave alone. This is particularly unfortunate as that's exactly the most important piece of information for the question to make sense.

Luuk's solution will give you two full sets of records - one with changed values and the other with the originals. Not what you desire if I understand your question correctly.

I can't include your criteria, of course, but I can show you what to fit your criteria into that will return what you request :
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf({YourCriteriaHere},'W' & Mid([ITEMNO], 2,24),[ITEMNO]) AS [ERPCODE]
Aug 19 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.