Replace prefix on a field within table

[Pilly170]

Hi,

I have a table, one field has data inserted with a prefix eg K 10292.
Is there an easy way to remove the "k" via VBA?? or am i looking to do an update query?

I did a search on the forums but couldnt find the answer, i can do it within excel but need to complete within access, any ideas?.

 

[hkaing79]

fld = "K 10292"
right (fld, len(fld) - 2) will give you "10292

 

[Pilly170]

and if i have to manipulate every row??

 

[PHV]

Something like this ?
UPDATE yourTable SET yourField=Mid(YourField,3)
WHERE yourField LIKE 'K %';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Pilly170]

for example then

Function UpdateDaums()
Dim strSQL As String


strSQL = "UPDATE Daums SET CustNo=Mid(CustNo,3)WHERE CustNo LIKE 'K %';"
DoCmd.SetWarnings warningsoff
DoCmd.RunSQL strSQL
DoCmd.SetWarnings warningson
End Function

 

[Pilly170]

Changed the % to * and it works fine... thanks