Sounds to me like you need to redefine your table structure and split this
field into three separate fields. However, until you accomplish this, you
will need to create a computed column that contains the value of the text
between the hyphens. You could do this with a complex computed column:
GroupByThis:Trim(Mid(YourField, instr(yourField, "-") + 1,
instrrev(yourField, "-") - 1 - instr(yourField, "-")))
Notice that this method requires Access to identify the position of the
first hyphen twice. This method also does not handle NULL values, so to
handle nulls, you would have to wrap all of the references to "yourField" in
the NZ() function. It also won't handle the situation where the data is
formatted incorrectly and contains less than 2 hyphens. Although this
doesn't take long, if you have lots of records, it could increase the query
time.
An alternative would be to write a function that will return the value, and
can handle all of the issues presented above, something like:
Public Function BetweenTheHyphens(SomeValue as Variant) as String
Dim intFirst as integer, intLast as Integer
if isnull(SomeValue) then
BetweenTheHyphens = ""
elseif len(SomeValue) - Len(Replace(SomeValue, "-", "")) < 2 then
BetweenTheHyphens = "Invalid data format"
Else
intFirst = instr(SomeValue, "-")
intLast = instrrev(SomeValue, "-")
BetweenTheHyphens = Trim(mid(SomeValue, intFirst+1, intLast -1
-intFirst))
End if
End Function
