Update Query Question

[Nancy]

This is what I want to do.

In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the
PO_ITEM and PO_NBR fields.
I want to parse into the actual fields of PO_ITEM and PO_NBR which will
exist in the same table.

Current Table Result
PO_ITEM_NBR PO_ITEM PO_NBR
123344 001 123344 001
123344 002 123344 002
123455 001 123455 001
etc.

I have created a updte query with the following SQL statememnt:

UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1),
PO_NBR = Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1);

When I attempt to do this, it asks for the PO_ITEM_NBR. I do not enter one
because I want the entire database.
It then gives me the error Datatype mismatch in criteria expression.

Why to I need to provide a PO_ITEM_NBR? How do I write this query to update
every row in access
without providing a PO_ITEM_NBR?

Thanks,

 

[Dale Fye]

Nancy,

Try wrapping PO_ITEM_NBR with brackets, everywhere you find it in your query.

But I'm confused, how do you get to the point that you have a PO_ITEM_NBR,
but don't already have PO_ITEM and PO_NBR? I assume that once you get
PO_ITEM and PO_NBR in your table, you are going to delete the column
PO_ITEM_NBR since you won't need it any more, and it violates the first
normal form of database design (No field will contain more than one data
element).

HTH
Dale

 

[Nancy]

This did not work. I get the same result. The reason I am doing this is
because I inherited this table and want to correct it so I may use it in a
form.

Anything else I can do? I really need this to work.

 

[John Spencer]

First check the name of the field the query is asking for and make sure you
haven't mistyped the field name.

Second, add a where clause so you won't try to process fields that are null
or don't have space in them.

UPDATE PO_TBL
SET PO_ITEM = Left([PO_ITEM_NBR],InStr([PO_ITEM_NBR]," ")-1),
PO_NBR = Mid([PO_ITEM_NBR],InStr([PO_ITEM_NBR]," ")+1)
WHERE PO_ITEM_NBR LIKE "* *"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..