Substitute text in string 1

[IamreallyStuck]

Hi,
I'm having trouble with using 'Substitute' in my query.

I have a table containing two fields, 'Squares' and 'For'.
'Squares' contains text enclosed in square brackets, and I want to get rid of the square brackets but keep the rest of the string.
'For' sometimes contains the word 'for' within the string. If the word 'for' is within the string, I want to get rid of the word 'for' and all the text before it. I.e., if the 'For' field contains "Present for Bob", I want the query to just return 'Bob'.

I've tried to use: SUBSTITUTE([Squares],"[","&quot and
SUBSTITUTE([For],"*for","&quot
as the criteria for my fields, but it does not seem to work.

Has anyone got a solution to this?
Thanks, Lee.

 

[PaulBricker]

This is an unusual one. To get rid of the brackets, check out the Replace Function. In a module put

Function SwapStuff(myStr as String) as String
Dim strHolder as String
strHolder = Replace(myStr, "[","&quot
SwapStuff = Replace(strHolder,"]","&quot
End Function

Then in the query put
MyField:SwapStuff([FieldName])

To get rid of the for use the Mid and Instr functions
MyValue:Mid([FieldName],Instr(1,[FieldName],"for&quot+1)

Paul

 

[IamreallyStuck]

Thanks Paul,
What you're suggesting seems fine, but i'm getting errors when I try to run it.

My module looks like this:
Option Explicit

Function SwapStuff(myStr As String) As String
Dim strHolder As String
strHolder = Replace(myStr, "[", "&quot
SwapStuff = Replace(strHolder, "]", "&quot
End Function

My query looks like this:
Field: Squares
Table: ReplaceText
Criteria: MyField:SwapStuff([Squares])

Field: For
Table: ReplaceText
Criteria: MyValue:Mid([For],Instr(1,[For],"for&quot+1)

I think that this follows your advice. Trouble is that I am getting this error message, and I don't know what to do to fix the problem (I'm new to Access):

"The expression that you entered has an invalid .(dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed paarentheses following the Null constant."

Sorry to be such a pain, but can you see what i'm doing wrong here?

Thanks,
Lee : )

 

[PaulBricker]

Put
MyField:SwapStuff([Squares])
and
MyValue:Mid([For],Instr(1,[For],"for&quot+1)

in new columns in the query, on the Field line, not the Criteria line of your fields.

Paul

 

[IamreallyStuck]

Works like a dream, thank you : )

p.s. I changed the number in the MyValue field to 3 to get rid of the whole word 'for'.

Thanks again,
Lee.

 

[IamreallyStuck]

Paul,
Just hit a snag - wonder if you could help with this? The code that you gave me is fine - unless the 'For' field in the table doesn't contain the word 'for'. In these situations your code cuts off the first 4 characters in the field.

Is it possible to sort this out with some kind of IIf expression? My Access help is not proving too helpful on this one!

Lee.

 

[PaulBricker]

Not sure why it would do that but you can try this.

MyValue:IIf(InStr(1,[For],"for&quot>0,Mid([For],Instr(1,[For],"for&quot+1), [For])

 

[PaulBricker]

You will need to change the 1 to 3 again to get rid the entire word "for". Forgot to update the expression.

Paul

 

[IamreallyStuck]

Thanks for your help Paul, it's working perfectly now!
Lee : )