I have set up a query with only one field and am attempting to write an expression
that will return only part of the text:
Red xxx Ford
Blue xxx Holden
White xxx Saab
The query result should only show the car makers.....Ford, Holden and Saab.
Hi Harry,
If car is *always* third element in string,
here be a function you can to save in a Module
(name of module should be different than function
name....happens all the time, seems like...name your
module something like "modUtilities")
if you are using Access 200x:
Public Function fNthElement(KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) As Variant
On Error GoTo Err_fNthElement
'adapted from function provided by Dirk Goldgar
Dim arrSegments As Variant
If Len(Trim(KeyString & "")) > 0 Then
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If ((ElementNo - 1) <= UBound(arrSegments)) _
And (ElementNo > 0) Then
fNthElement = arrSegments(ElementNo - 1)
Else
fNthElement = Null
End If
Else
fNthElement = Null
End If
Exit_fNthElement:
Exit Function
Err_fNthElement:
MsgBox Err.Description
Resume Exit_fNthElement
End Function
To use in query,
fNthElement([yourfield]," ",3)
/////////////////////
If you don't know how many "elements"
will be in the field string, but you always
want to get the last element:
Public Function fLastElement(KeyString As Variant, _
Delimiter As String) As Variant
On Error GoTo Err_fLastElement
Dim arrSegments As Variant
If Len(Trim(KeyString & "")) > 0 Then
arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
If LBound(arrSegments)<>UBound(arrSegments) Then
fLastElement = arrSegments(UBound(arrSegments))
Else
fLastElement = KeyString
End If
Else
fLastElement = Null
End If
Exit_fLastElement:
Exit Function
Err_fLastElement:
MsgBox Err.Description
Resume Exit_fLastElement
End Function
To use in query,
fLastElement([yourfield]," ")
This may be "overkill" but my bet is that
need for them will come up from time to
time, and its one less thing you have to
think about...just copy "modUtilities"
into your new project.
///////////////////////
One other alternative (again in Access 200x)
is to use the InStrRev function.
?InStrRev("Red xxx Ford"," ",-1,1)
8
?Mid("Red xxx Ford",InStrRev("Red xxx Ford"," ",-1,1)+1)
Ford
So to use in your query
Mid([yourfield],InStrRev([yourfield]," ",-1,1)+1)
Please respond back if I have misunderstood
or was not clear about something.
Good luck,
Gary Walter
