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" <BlackKnight@discussions.microsoft.com> wrote in message 
news:86C78273-0BAC-4B5B-9127-E7F0B4B11DD6@microsoft.com...
> 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?
> > .
> >