Change Character upon Select

[ShafiqqAziz]

SELECT "W" & Mid([ITEMNO],2,24) AS ERPCODE

This code work perfectly well to change all the first character from DB to W. The problem is, I have one more data that I don't want to change the first character.
Tried stackoverflow's solution but can't get it work (Cannot give link due to new comer). Any idea to solve my problem?:banghead:

 

[arnelgp]

The one you don't want to change? Can you give as a clue? Starts with...

 

[jdraw]

Further to arnelgp's request, please show us the full code you re using.
If you are making a permanent change, you probably need an UPDATE query.

But more info is required in order to give a focused response.

 

[ShafiqqAziz]

The one you don't want to change? Can you give as a clue? Starts with...

Okay, lets say i have 3 data from my DB.

1. E1234
2. S1234
3. NYB123

My code can change all first letters to W, but i dont want to change the NYB123 to WYB123, i want that data untouched. i managed to change the first letter to W, so i think the code perfectly working.
So, the end result should be like this:

1. W1234
2. W1234
3. NYB123

Is my explanation clear?

 

[Minty]

Not really - you have told us that you do want to change anything beginning with E or S but not NYB . Is that the complete set of rules ?

What exactly do you want to change and NOT change?

Or is it that you only want to change things that have a single letter at the beginning but not multiple letters?

 

[jdraw]

Something along this line might work (concept only)

SELECT "W" & Mid([ITEMNO],2,24) AS ERPCODE FROM YourTableName
Where Left(ITEMNO,3) <> "NYB"

 

[ShafiqqAziz]

Not really - you have told us that you do want to change anything beginning with E or S but not NYB . Is that the complete set of rules ?

What exactly do you want to change and NOT change?

Or is it that you only want to change things that have a single letter at the beginning but not multiple letters?

Okay, lets start this over.

I have 3 set of data.

1. E1234
2. S1234
3. NYB123

What i want to change is data number 1 & 2, change only the first letter from E & S to W. What i dont want to change is data number 3. So the end result should be:

1. E1234 -> W1234
2. S1234 -> W1234
3. NYB123

What my code do here is, it change all the first letter to W.
The screenshot is what i'm trying to follow.

 

[Minty]

I fully understand what you have said - what I was asking was - Is that the ONLY criteria.

Are there any other letters or combinations of letters that you might want to change or not change?

If not then JDraw's solution will do what you want.

 

[arnelgp]

IIf(IsNumeric(Mid([FieldName],2,1)),"W" & Mid([FieldName],2),[FieldName])

 

[ShafiqqAziz]

I will try each of the solution tomorrow and be updating here.

 

[jdraw]

It isn't so much trying each suggestion. It is determining/confirming WHAT exactly is/are the criteria involved.

 

[ShafiqqAziz]

Something along this line might work (concept only)

SELECT "W" & Mid([ITEMNO],2,24) AS ERPCODE FROM YourTableName
Where Left(ITEMNO,3) <> "NYB"

Screenshot is the result of my code. POE Item is the original name of the data, the ERPCODE is the changed name. So what I want to do here is, any POE Item name start with E-* and S-* change to W-*, any name start with NYB* should not be touched or remove, leave the ERPCODE as POE Item. The code start from Mid to closing bracket is not touching after the first character.

The rules for this are:
1. Shows every available data.
2. Change first letter of the data form E-* and S-* to W-*.
3. Shows data start with N but not change the first letter.

I'm sorry if my explanation this time still confusing you all. I'm not good explaining problems and this is the first time I'm exploring MS Access.

 

[arnelgp]

this is the expression in your query.
replace Field1 with actual fieldname.

IIf([Field1] Like "[!Nn]*","W" & Mid([Field1],2),[Field1])

 

[ShafiqqAziz]

this is the expression in your query.
replace Field1 with actual fieldname.

IIf([Field1] Like "[!Nn]*","W" & Mid([Field1],2),[Field1])

Worked like charm! Thank you very2 much!!!

 

[ShafiqqAziz]

Okay guys, another problem appeared when I'm importing from my excel. Maybe because of the NOT LIKE syntax. Can you guys help me again?:banghead:

 

[ShafiqqAziz]

Okay guys, another problem appeared when I'm importing from my excel. Maybe because of the NOT LIKE syntax. Can you guys help me again?:banghead:

Okay, forget it. I've found the solution. Thank you again guys