Another replacing value from query question

[Villagio]

Hi,

I retrieve formula file from a ERP system. Due to the legacy ERP, the
formula can only contain 40 ingredients or less. In order to continue with
41 or more ingredients, we create another formula named #A00001X to
associate with parent formula called A00001.

After I retrieve those data from ERP to MS Access, the data structure
look like this:

FormulaCode Material Code Parts

A00001 *0001 10.00
A00001 *0008 15.50
~~~~~~~~ ~~~~~~ ~~~~~
~~~~~~~~ ~~~~~~ ~~~~~
#A00001X *0238 5.45
#A00001X *0357 4.28


The above table has over 250,000 records

How do I use UPDATE and REPLACE to delete FormulaCode field that has #
at the beginning and X at the end? Thanks.

Peter

 

[John Spencer]

Do you want to delete the record or just update the field to null or
update the field to strip off the leadind # and trailing X?

UPDATE YourTable
SET FormulaCode = Mid([FormulaCode],2,Len([FormulaCode])-2)
WHERE FormulaCode Like "[#]*X"

To set Formula code to null
UPDATE YourTable
SET FormulaCode = Null
WHERE FormulaCode Like "[#]*X"

To delete the entire records
DELETE FROM YourTable
WHERE FormulaCode Like "[#]*X"

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

 

[Villagio]

John,

I just wanted to strip off the leading # and trailing X, so the formulaCode
will be all A00001 for example.

Peter

Do you want to delete the record or just update the field to null or
update the field to strip off the leadind # and trailing X?

UPDATE YourTable
SET FormulaCode = Mid([FormulaCode],2,Len([FormulaCode])-2)
WHERE FormulaCode Like "[#]*X"

To set Formula code to null
UPDATE YourTable
SET FormulaCode = Null
WHERE FormulaCode Like "[#]*X"

To delete the entire records
DELETE FROM YourTable
WHERE FormulaCode Like "[#]*X"

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

Hi,

I retrieve formula file from a ERP system. Due to the legacy ERP, the
formula can only contain 40 ingredients or less. In order to continue with
41 or more ingredients, we create another formula named #A00001X to
associate with parent formula called A00001.

After I retrieve those data from ERP to MS Access, the data structure
look like this:

FormulaCode Material Code Parts

A00001 *0001 10.00
A00001 *0008 15.50
~~~~~~~~ ~~~~~~ ~~~~~
~~~~~~~~ ~~~~~~ ~~~~~
#A00001X *0238 5.45
#A00001X *0357 4.28


The above table has over 250,000 records

How do I use UPDATE and REPLACE to delete FormulaCode field that has #
at the beginning and X at the end? Thanks.

Peter

 

[Villagio]

I tried this:

UPDATE FormulaTable SET FormulaTable.formula =
REPLACE([formula],"#A[*]X","A[*]");

but nothing got updated. Where am I missing?

 

[John Spencer]

Replace does not use wildcards. Did you try the first query I posted?

UPDATE FormulaTable
SET Formula = Mid([Formula],2,Len([Formula])-2)
WHERE Formula Like "[#]*X"

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

I tried this:

UPDATE FormulaTable SET FormulaTable.formula =
REPLACE([formula],"#A[*]X","A[*]");

but nothing got updated. Where am I missing?

Hi,

I retrieve formula file from a ERP system. Due to the legacy ERP, the
formula can only contain 40 ingredients or less. In order to continue with
41 or more ingredients, we create another formula named #A00001X to
associate with parent formula called A00001.

After I retrieve those data from ERP to MS Access, the data structure
look like this:

FormulaCode Material Code Parts

A00001 *0001 10.00
A00001 *0008 15.50
~~~~~~~~ ~~~~~~ ~~~~~
~~~~~~~~ ~~~~~~ ~~~~~
#A00001X *0238 5.45
#A00001X *0357 4.28


The above table has over 250,000 records

How do I use UPDATE and REPLACE to delete FormulaCode field that has #
at the beginning and X at the end? Thanks.

Peter

 

[Villagio]

John,

Great, this one worked. Thanks.

Peter

Replace does not use wildcards. Did you try the first query I posted?

UPDATE FormulaTable
SET Formula = Mid([Formula],2,Len([Formula])-2)
WHERE Formula Like "[#]*X"

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

I tried this:

UPDATE FormulaTable SET FormulaTable.formula =
REPLACE([formula],"#A[*]X","A[*]");

but nothing got updated. Where am I missing?

Hi,

I retrieve formula file from a ERP system. Due to the legacy ERP, the
formula can only contain 40 ingredients or less. In order to continue with
41 or more ingredients, we create another formula named #A00001X to
associate with parent formula called A00001.

After I retrieve those data from ERP to MS Access, the data structure
look like this:

FormulaCode Material Code Parts

A00001 *0001 10.00
A00001 *0008 15.50
~~~~~~~~ ~~~~~~ ~~~~~
~~~~~~~~ ~~~~~~ ~~~~~
#A00001X *0238 5.45
#A00001X *0357 4.28


The above table has over 250,000 records

How do I use UPDATE and REPLACE to delete FormulaCode field that has #
at the beginning and X at the end? Thanks.

Peter