field update

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

Guest

please help me!!
I have field containing rows like this:

hamburger, S 31-41
S, 191-210
..
..
..
and so on.
can i delete all the string and the " , " with a single query?
 
UPDATE tableName SET fieldName = REPLACE( fieldName, ",", "" )



(Access 2002 or later)



Hoping it may help,
Vanderghast, Access MVP
 
thank you for your suggestion but i'm currently using microsoft Access 2000.
but thanks anyway.

Novianto Dharma
 
In Access 2000, there is a small bug that does not allow to use Replace in a
query. The workaround is to define a function, in a standard module:




Public Function myReplace( myArg1 As String, myArg2 As String, myArg3 AS
String) As String
myReplace = Replace(myArg1, myArg2 , myArg3)
End Function




and then, in the query:



UPDATE tableName SET fieldName = myReplace( fieldName, ",", "" )



Hoping it may help,
Vanderghast, Access MVP
 
What do you want as the result?
Do you want these values returned for your two sample values?
31-41
191-210

Is the value to be returned always after the last space?
Does the value always contain a comma?
Is the value always a two numbers separated by a dash with no other
characters between the dash and the numbers?
Is the value always at the end?


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

almost all value contains comma, ":" , and it is not separated with other
char and it always at the end. is it still possible? remember that i am using
access 2000.

Thanks in advance.

Novianto Dharma
 
So you want the last set of character in the field?

If you are using Access 2000 and it is up to date on its patches and
assuming that the last set of characters is preceded by a space and those
are the characters you want.

Try the following:

Mid([YourField],InstrRev([YourField]," ")+1)

That looks for the last space in the field and then returns everything after
the last space.

Normal advice: BACKUP your data before you use this in an update. You
cannot undo this if it is wrong.

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