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
|
|||
|
|||
Inserting a character
In a table I have a textfield containg data like B09-001, B09-002 etc.
Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? |
#2
|
|||
|
|||
Inserting a character
If you have a single field into which you have stuffed two (or more) facts
(i.e., "B09" and apparently a sequence number), you have (re-)discovered why it is not good database design to stuff two (or more) facts into a single field! Before you proceed any further, consider creating two new fields in your database, one for each fact. Then you can use a query to concatenate the first fact ("B09") with a hyphen ("-") and the sequence number, formatted any way you want. And if the "B09-" is constant, you don't even need the first field, just a sequence number field! Good luck! -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "BlackKnight" wrote in message ... In a table I have a textfield containg data like B09-001, B09-002 etc. Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? |
#3
|
|||
|
|||
Inserting a character
You might use an update query. For your very specific example that could look
like the following. UPDATE YourTable SET YourField = Replace([YourField],"-","-0") WHERE YourField LIKE "B09-###" ==Create a new query ==Add your table ==Add your field ==Set the criteria under field to be changed to LIKE "B09-###" ==SELECT Query: Update from the menu ==In the Update to "box" under the field enter Replace([YourField],"-","-0") The above assumes you are using Access 2000 or later. And if you are using Access 2000 that it is patched to SP-3 level. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County BlackKnight wrote: In a table I have a textfield containg data like B09-001, B09-002 etc. Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? |
#4
|
|||
|
|||
Inserting a character
I am using Access97.
I already came as far as using a update query, and using LIKE "B09-###" in the criteria box. But what to put in the Update to box is my problem. The expression "Replace([YourField],"-","-0")" does not work. Is this because I use Access97? "John Spencer" wrote: You might use an update query. For your very specific example that could look like the following. UPDATE YourTable SET YourField = Replace([YourField],"-","-0") WHERE YourField LIKE "B09-###" ==Create a new query ==Add your table ==Add your field ==Set the criteria under field to be changed to LIKE "B09-###" ==SELECT Query: Update from the menu ==In the Update to "box" under the field enter Replace([YourField],"-","-0") The above assumes you are using Access 2000 or later. And if you are using Access 2000 that it is patched to SP-3 level. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County BlackKnight wrote: In a table I have a textfield containg data like B09-001, B09-002 etc. Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? . |
#5
|
|||
|
|||
Inserting a character
There is no REPLACE function in Access 97. That is what the last line of my
first post was meant to point out. I see that I was unclear. You can accomplish the same thing using a variation on the query If you are always looking at the exact structure B09-### UPDATE YourTable SET YourField = "B09-0" & Right([YourField],3) WHERE YourField LIKE "B09-###" If not then UPDATE YourTable SET YourField = Left([YourField],Instr(1,[YourField],"-")) & "0" & Mid([YourField],Instr(1,[YourField],"-")+1) WHERE YourField LIKE "B09-###" OR if the "BO9-" is ALWAYS there as the leading characters UPDATE YourTable SET YourField = "B09-0" & Mid([YourField],Instr(1,[YourField],"-")+1) WHERE YourField LIKE "B09-###" John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County BlackKnight wrote: I am using Access97. I already came as far as using a update query, and using LIKE "B09-###" in the criteria box. But what to put in the Update to box is my problem. The expression "Replace([YourField],"-","-0")" does not work. Is this because I use Access97? "John Spencer" wrote: You might use an update query. For your very specific example that could look like the following. UPDATE YourTable SET YourField = Replace([YourField],"-","-0") WHERE YourField LIKE "B09-###" ==Create a new query ==Add your table ==Add your field ==Set the criteria under field to be changed to LIKE "B09-###" ==SELECT Query: Update from the menu ==In the Update to "box" under the field enter Replace([YourField],"-","-0") The above assumes you are using Access 2000 or later. And if you are using Access 2000 that it is patched to SP-3 level. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County BlackKnight wrote: In a table I have a textfield containg data like B09-001, B09-002 etc. Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? . |
#6
|
|||
|
|||
Inserting a character
In the meantime I figured it out myself.
In entered "Left(["MyField];4)+"0"+Right([MyField];3)" in the update to box. "BlackKnight" wrote: I am using Access97. I already came as far as using a update query, and using LIKE "B09-###" in the criteria box. But what to put in the Update to box is my problem. The expression "Replace([YourField],"-","-0")" does not work. Is this because I use Access97? "John Spencer" wrote: You might use an update query. For your very specific example that could look like the following. UPDATE YourTable SET YourField = Replace([YourField],"-","-0") WHERE YourField LIKE "B09-###" ==Create a new query ==Add your table ==Add your field ==Set the criteria under field to be changed to LIKE "B09-###" ==SELECT Query: Update from the menu ==In the Update to "box" under the field enter Replace([YourField],"-","-0") The above assumes you are using Access 2000 or later. And if you are using Access 2000 that it is patched to SP-3 level. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County BlackKnight wrote: In a table I have a textfield containg data like B09-001, B09-002 etc. Now I am almost at B09-999, so I like to insert a "0" so that the data look like B09-0001, B09-0002, B09-0999 etc. How can I do that with a update-query? . |
Thread Tools | |
Display Modes | |
|
|