Replace Alphabet...

[hrprabhu]

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..

[NeoPa]

You will need a SELECT query something like :

SELECT [Service]
     , IIf([YourTable].[Service No] Like '[A-Z]#*',Mid([YourTable].[Service No],2,999),[YourTable].[Service No]) AS [NumPart]
     , Choose([Service],[NumPart]
                       ,IIf([NumPart] Like '8#*','O','A')+[NumPart]
                       ,IIf([NumPart] Like '8#*','O','N')) AS [Service No]
FROM   [YourTable]

[hrprabhu]

Thank you NeoPa, that was helpful...

[twinnyfo]

@NeoPa,

A B S O L U T E L Y B R I L L I A N T!

[NeoPa]

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.