If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Replace Function
I am trying to replace a single letter in a field, but with in several cells
may be a different letter. I can successfully remove one letter but if I try to create a formula to replace for example, "a", replace "s", etc... I cannot get this to work. Below is an example of the data elements: a4000 c4011 d2011 I need to get rid of the letter that preceeds the series of numbers. Can someone please assist me with creating a formula/function that will remove the letters. Thanks, Lisa W. |
#2
|
|||
|
|||
Replace Function
Hello Lisa,
You are going to have to start with defining (for yourself, and [you may have dones so already) telling us) exactly what you want to replace and under what conditions. Is it simply (always) the first character of the field? |
#3
|
|||
|
|||
Replace Function
Hello Fred,
Yes I want to replace the first character of every field. It's just that the first character could be the letter "a", "s", "d", or "c". Thank You! Lisa W. "Fred" wrote: Hello Lisa, You are going to have to start with defining (for yourself, and [you may have dones so already) telling us) exactly what you want to replace and under what conditions. Is it simply (always) the first character of the field? |
#4
|
|||
|
|||
Replace Function
On Wed, 15 Jul 2009 08:31:03 -0700, Lisa W. wrote:
Hello Fred, Yes I want to replace the first character of every field. It's just that the first character could be the letter "a", "s", "d", or "c". Thank You! Lisa W. "Fred" wrote: Hello Lisa, You are going to have to start with defining (for yourself, and [you may have dones so already) telling us) exactly what you want to replace and under what conditions. Is it simply (always) the first character of the field? You don't get extra points for brevity. You may know what you want but we can't see your computer nor read your mind. It's still not clear. Where are you doing this? in a Query, on a Form, in a Report? Do you wish to permanently change the table field's data, or just display the existing data differently on a form or report? To just display the data.... To always remove the first character (whatever that character is) in a Select query: NewColumn:Mid([FieldName],2) or to remove the first characters only if it is one of the 4 mentioned above, in a query: NewColumn:IIf(Left([Fieldname],1) In ("a","s","d","c"), Mid([FieldName],2),[FieldName]) To Remove that first character ONLY if it is one of the 4 you mentioned above, using the Replace function, (in a query): NewColumn:Replace(Replace(Replace(Replace(Replace([FieldName],"a",""),"s","")"d",""),"c","") Note the above Replace() functions will remove those characters anywhere in the field, not just the first ones. All the above would be used in a Select query and then [NewColumn] would be shown on the form or in the report. To permanently change the field's data you would use the above expressions in an Update query. For Example ... Update YourTable Set YourTable.[FieldName] = Mid([FieldName],2) Where Left([FieldName],1) In ("a","s","d","c") -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#5
|
|||
|
|||
Replace Function
Thank you! I will use in a query.
Apologies for not giving you full details...:-( "fredg" wrote: On Wed, 15 Jul 2009 08:31:03 -0700, Lisa W. wrote: Hello Fred, Yes I want to replace the first character of every field. It's just that the first character could be the letter "a", "s", "d", or "c". Thank You! Lisa W. "Fred" wrote: Hello Lisa, You are going to have to start with defining (for yourself, and [you may have dones so already) telling us) exactly what you want to replace and under what conditions. Is it simply (always) the first character of the field? You don't get extra points for brevity. You may know what you want but we can't see your computer nor read your mind. It's still not clear. Where are you doing this? in a Query, on a Form, in a Report? Do you wish to permanently change the table field's data, or just display the existing data differently on a form or report? To just display the data.... To always remove the first character (whatever that character is) in a Select query: NewColumn:Mid([FieldName],2) or to remove the first characters only if it is one of the 4 mentioned above, in a query: NewColumn:IIf(Left([Fieldname],1) In ("a","s","d","c"), Mid([FieldName],2),[FieldName]) To Remove that first character ONLY if it is one of the 4 you mentioned above, using the Replace function, (in a query): NewColumn:Replace(Replace(Replace(Replace(Replace([FieldName],"a",""),"s","")"d",""),"c","") Note the above Replace() functions will remove those characters anywhere in the field, not just the first ones. All the above would be used in a Select query and then [NewColumn] would be shown on the form or in the report. To permanently change the field's data you would use the above expressions in an Update query. For Example ... Update YourTable Set YourTable.[FieldName] = Mid([FieldName],2) Where Left([FieldName],1) In ("a","s","d","c") -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Replace Function
Permanently or only temporarily
Temporary (calculated field) Field: StripIt: IIF([MyField] like "[A-Z]*",Mid([MyField],2),[MyField]) A permanent change could use an update query that looked like the following UPDATE [MyTable] SET [MyField] = Mid([MyField]) WHERE [MyField] like "[A-Z]*" John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Lisa W. wrote: I am trying to replace a single letter in a field, but with in several cells may be a different letter. I can successfully remove one letter but if I try to create a formula to replace for example, "a", replace "s", etc... I cannot get this to work. Below is an example of the data elements: a4000 c4011 d2011 I need to get rid of the letter that preceeds the series of numbers. Can someone please assist me with creating a formula/function that will remove the letters. Thanks, Lisa W. |
Thread Tools | |
Display Modes | |
|
|