472,958 Members | 2,330 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 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 1533
32,547 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
3,653 Expert Mod 2GB

A B S O L U T E L Y B R I L L I A N T!
Mar 9 '15 #4
32,547 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

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...
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...
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...
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...
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. ...
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...
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...
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?...
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...
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...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

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.