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?

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?

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?

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

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

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