extracting string - trimming

[bimmer5]

I need to trim the following query field: "LQ2daily.description" from both left and right side. The field contains text strings like this:

FG - Prod - Mfg Sub-Totals ---
FG - Prod - Mfg - Whole Grain Sub-Totals ---
FG - Prod - Mfg - Egg Sub-Totals ---

On the left side I need to trim "FG - " (everything before "Prod") and on the right side I need to trim " Sub-Totals ---" leaving me with the following:

Prod - Mfg
Prod - Mfg - Whole Grain
Prod - Mfg - Egg

Thank you.

 

[RuralGuy]

Try this function in a standard module:

Function MyString(strIn As String) As String

   MyString = Mid(strIn, InStr(strIn, "Prod"))
   MyString = Left(MyString, InStrRev(MyString, " Sub-Total") - 1)

End Function

 

[raskew]

Hi -

Similar way to attack:

x = "FG - Prod - Mfg - Whole Grain Sub-Totals ---"
? trim(mid(left(x, instr(x, "Sub")-1), instr(x, "-")+1))
Prod - Mfg - Whole Grain

Bob

 

[bimmer5]

Guys,

we can't build the function based on the context of the string (this applies at least to the left side side of the string). The "Prod" part of the string changes to something else. Please take a look at the attachment with the real data.
I appologize for not being clear at the first place.

 

[RuralGuy]

It looks like what you want always starts at the 6th character so...:

Function MyString(strIn As String) As String

   MyString = Mid(strIn, 6)
   MyString = Left(MyString, InStrRev(MyString, " Sub-Total") - 1)

End Function

 

[RuralGuy]

It looks like Bob's code will work as it was originally posted assuming there will always be only one "- " before the characters you need.

 

[bimmer5]

Rural Guy,

pasting your command into SQL View gives me an error:

SELECT LQ2.description
Function MyString(strIn As String) As String
MyString = Mid(strIn, 6)
MyString = Left(MyString, InStrRev(MyString, " Sub-Total") - 1)
End Function
FROM LQ2;

The error message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Do I have to change something in the code?
Thank you for your effort.

 

[RuralGuy]

You would need to put my function in a standard module name anything *but* MyString. Then in SQL you would invoke it with:
SELECT MyString(description) As AlteredString
FROM LQ2;

 

[bimmer5]

Rural Guy,

could you PLEASE update the attachment I posted here so that I can see what you meant. I'm sorry but I am not an expert in this.

 

[Mike375]

This works on the products in your query

NewFieldName: Mid([description],6,InStr([description],"sub")-6)

It is depending on the first part of the string to be lifted as character 6

 

[bimmer5]

Mike,
that's what I was looking for, works like a charm!!!
Thank you very much to all who participated here, special thanks to Mike.
Peter