468,134 Members | 1,203 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,134 developers. It's quick & easy.

Replace Alphabet...

Hi Everyone...

Attached is a small database with one table with two columns as shown below.

Service Service No.
1 F327445
1 421547
1 8014432
2 O22771
2 L185244
2 O852424
3 O321322
3 R552552
3 O8471254

Basically I want to do the following

If service is 1 remove the alphabet

If service is 2 remove the what ever the alphabet and replace it with A unless the numerical parts starts with 8 then replace with O

If service is 2 remove the what ever the alphabet and replace it with N unless the numerical parts starts with 8 then replace with O

I want the out put as follows....

Service Service No.
1 327445
1 421547
1 8014432
2 A22771
2 A185244
2 O852424
3 N321322
3 N552552
3 O8471254

thank you..
Attached Files
File Type: zip Remove_Alphabet.zip (31.7 KB, 44 views)
Mar 8 '15 #1
4 1092
NeoPa
32,036 Expert Mod 16PB
You will need a SELECT query something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Service]
  2.      , IIf([YourTable].[Service No] Like '[A-Z]#*',Mid([YourTable].[Service No],2,999),[YourTable].[Service No]) AS [NumPart]
  3.      , Choose([Service],[NumPart]
  4.                        ,IIf([NumPart] Like '8#*','O','A')+[NumPart]
  5.                        ,IIf([NumPart] Like '8#*','O','N')) AS [Service No]
  6. FROM   [YourTable]
Mar 9 '15 #2
Thank you NeoPa, that was helpful...
Mar 9 '15 #3
twinnyfo
3,638 Expert Mod 2GB
@NeoPa,

A B S O L U T E L Y B R I L L I A N T!
Mar 9 '15 #4
NeoPa
32,036 Expert Mod 16PB
Why, thank you TwinnyFo.

I work in Access nearly all the time now so I've picked up a few tricks along the way. Actually, this is a good illustration of what you can do when you stray a little way off the beaten track. The format of Like strings and the Choose() function can both be very useful to know I've found.
Mar 10 '15 #5

Post your reply

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

Similar topics

2 posts views Thread by mrbog | last post: by
3 posts views Thread by - ions | last post: by
5 posts views Thread by Stefan Krah | last post: by
5 posts views Thread by Dan | last post: by
3 posts views Thread by Raed Sawalha | last post: by
31 posts views Thread by Joe Smith | last post: by
23 posts views Thread by Umesh | last post: by
20 posts views Thread by geebanga88 | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.