Append Query Problem

[Guest]

Howdy from Oklahoma!!!

I am new to Access and Append queries but I am needing to build an Append
Query to Append (or Copy) rows from a table but I am only wanting to change a
part of the Primary Key for the new rows.

Let me try to explain...
I have a table of Work Orders that have the Work Order # as the Primary Key
and the Work Order# is in the form of "ABC1234CDE" where I just need to
change the "1234" part of the Work Order# to "6789" for the new record. So
when executed the Append query will add a new Row of data exactly like the
original (all other fields the same) except that the new Work Order# will be
"ABC6789CDE". Also I want to execute this from a form that the person puts in
the new "6789" in a field on the form and then hits a button to execute. I
can build the form and the button with no problem, I just am not sure how to
make it pull from the form to update the new Work Order #.

I cannot find anyway of doing this partial update of a field when working in
an Append query. I have only built a couple of other Append Queries and they
were very simple, straight-forward queries.

Any help would be greatly appreciated, and as always THANKS IN ADVANCE!!!

Chip

 

[Phil]

Two things. One, taking it appart. Your Work order number has threee
pieces.

ABC, 1234, and CDE.
Assuming this is a fixedwidth field, (each of your pieces will be the
same length, you can use Left() right() and Mid(). These return a piece
of a string.

For instance

Left([workOrder#],3) will return the first three letters in the string,
or "ABC"
Right([workOrder#],3) would give you the last three letters, or "CDE"
Mid(([workOrder#],4,4) would give you a string of 4 characters starting
with the 4th character in the string, or "1234". Note the first 4 is
the where to start your string from, and the second is how many letters
do you want.

"&" puts strings together. (Concatenates)
So if [workorder#]="ABC1234CDE"
Left([workOrder#],3)&"6789"&Right([workOrder#],3)=ABC6789CDE

You could of course get the "6789" from a form control or whatever.

Help any?