Manipulating String values

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi all !

In Access 2000...

If I wanted to update table records to a different value, how is this done

eg if I have a field called 'classes' which contained data like:

IAP ROUTEWAY TRAINING 13 Wks
IAP ROUTEWAY TRAINING 26 Wks
IAP ROUTEWAY TRAINING Extension 13 Wks
IAP ROUTEWAY Extension 26 Wks
IAP ROUTEWAY 13 Wks
IAP ROUTEWAY Extension 13 Wks

and I wanted to change (update) these values into the following format:

IAP Training 13
IAP Training 26
IAP Training Ext 13
IAP Ext 26
IAP 13
IAP Ext 13

ie would like to get rid of 'ROUTEWAY' and 'Wks' ?

how would this be done? Can it be done within an IIF statement inside the
update query or is there some kind of clever code which can iterate through
the records and update the values?

Cheers

Jake
 
Hi all !

In Access 2000...
Click to expand...

Thanks... it makes a difference! This can be done in 97, 2000, or
XP... but DIFFERENTLY in all three.
ie would like to get rid of 'ROUTEWAY' and 'Wks' ?
Click to expand...

You'll need to use the REPLACE function, but unaccountably in A2000 it
cannot be used in Queries (they fixed this oversight in XP). Create a
new Module and type into it:

Public Function QReplace(strIn As String, strOld As String, _
strNew As String) As String
QReplace = Replace(strIn, strOld, strNew)
End Function

Save the Module with some name OTHER than QReplace (the names must be
unique within the database), say basUtilities.

Then run an Update query updating the field to

REPLACE([Fieldname], "ROUTEWAY ", "")

and then another similar query replacing "Wks".
 
Back
Top