Goal: To remove "S - Bayside Peninsula - " from every record. But not all records hve the last dash

[MJays]

Hi All,

I'm a newby, so not sure if I'm doing this right, but I have a problem I can't work out. (Too old and gnarly.)

An excel spreadsheet is provided to us from an external source. We can't change the way they collect it. But we need to run it through Access to create several detailed reports.

In this case, I need to remove "S - Bayside Peninsula - " from all records in a query, but keep the data that follows the last dash (and space). However, some of the records do not contain the last dash, and have just the space before the data I need to keep.

I have been trying to work out how to do an IIf to add a dash to those records that don't have one, then use a String Function to remove what I don't want.

I'm not an Access wizard.... just trying to help my colleagues who are even worse than I am!!



Example:

S - Bayside Peninsula - Contracted Case Management 1
S - Bayside Peninsula Investigation Team 7

Any assistance would be greatly appreciated.

MJ (Mary-Jane)

[ranman256]

In the import query,(or update qry)
set 1 field to filter only the S recs, then change it:

Criteria:
left([field],3) ="S -"

Update to:
Mid([field],4)

[CJ_London]

how about

trim(replace(replace(myfield,"S - Bayside Peninsula",""),"-",""))

or if all records start with 'S - Bayside Peninsula'

trim(replace(mid(myfield,22),"-","")

[ArviLaanemets]

replace(replace(myfield,"S - Bayside Peninsula -",""),"S - Bayside Peninsula",""))

The idea is, that 1st replace searches for longer string, and when it finds it, then replaces it with empty string, otherwise doesn't change it. After that, second replace searches for shorter string (when 1st search did replacing, then obviously it is not found now), and when it finds it, then replaces it with empty string, otherwise doesn't change it.

(Ajax solution works only, when dash is not used anywhere else in same field in any row. Probably it will work too, but it is not foolproof.)

[MJays]

Thank you, Ajax.

Both options worked perfectly.