473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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, 55 views)
Mar 8 '15 #1
4 1551
NeoPa
32,556 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,653 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,556 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

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

Similar topics

2
by: mrbog | last post by:
$ar=array("a","b","c"); $string="The third letter of the alpabet is %%2%% and the second is %%1%%." $string = preg_replace('/{2}(.*?){2}/', '$ar', $string); echo $string; I want to see: "The...
3
by: - ions | last post by:
Hi, i would like to know how to replace every char in a string with a certin given char using the String.replace(char oldChar,char newChar). I would like to replace all letters with an underscore...
5
by: Stefan Krah | last post by:
Hello, I am currently writing code where it is convenient to convert char to int . The conversion function relies on a character set with contiguous alphabets. int set_mesg(Key *key, char...
12
by: one | last post by:
greetings i am just wondering if some expert here can either show me how to do this or point me to the right direction (url... i want to use c# to generate a list of alphabet e.g A B C ... AA...
8
by: Jack Addington | last post by:
I want to scroll through the alphabet in order to scroll some data to the closest name that starts with a letter. If the user hits the H button then it should scroll to the letter closest to H. ...
5
by: Dan | last post by:
Okay...one last question. I've pretty much spent the weekend reading up on .Net, Soap, web services and enough three letter acronyms to feed the world alphabet soup. Is it a fair and mostly...
3
by: Raed Sawalha | last post by:
I have the following letters; string letters = "a;b;c....to z"; the I need to replace the incoming string which containing letters above with integer 1 i did following for(int...
31
by: Joe Smith | last post by:
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" "abcdefghijklmnopqrstuvwxyz" "0123456789" " " "!#%^&*()-_" "+=~\|;:\'" "\"{},.<>/\?" "\a\b\f\n\r\t\v\\" Do the above string literals comprise an alphabet for C?...
23
by: Umesh | last post by:
This is a basic thing. Say A=0100 0001 in ASCII which deals with 256 characters(you know better than me!) But we deal with only four characters and 2 bits are enough to encode them. I want to...
20
by: geebanga88 | last post by:
HI i have a method that is supose to store the alphabet in an array however dont think that it is being added to the array. public static void GetAlphabet (char alphabet) { int...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.