Append Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
Back
Top