Last characters of a string

[Jim]

I'm trying to find a way to sort a query using the
characters at the end of a field following the last dash.
For example a field contains;
1-2-3
123-12-37
a1-b1-c1
1-1-12345
112-444-681012

I would like to sort based on the the characters after the
last dash. Any ideas?

Thanks,

Jim

 

[RobFMS]

A *quick* thought would be to use the Split() function with the "-"
character as the delimiter. The last element in the array would be the value
you are looking to sort on.

HTH

--

Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Problems with your database? Need to upgrade your application?
Contact the FMS Professional Solutions Group: www.fmsinc.com/consulting

Need a Book Recommendation?
www.fmsinc.com/toplevel/books.htm

Need software tools for Access, VB or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Jim]

Sounds good. Would you have an example? Nothing in help
files.

Thanks,

Jim

 

[John Spencer (MVP)]

What version of Access? You could try the InStrRev function in 2002 and later.
In 2000, I believe you have to write a wrapper user function for instrRev to
work. I'm not sure of the syntax for InstrRev, so the following example may be
off.

Field: LastChars: Mid(FieldName,InstrRev([FieldName],"-"))

Then Order By this calculated field.

 

[Fredg]

I'm trying to find a way to sort a query using the
characters at the end of a field following the last dash.
For example a field contains;
1-2-3
123-12-37
a1-b1-c1
1-1-12345
112-444-681012

I would like to sort based on the the characters after the
last dash. Any ideas?

Thanks,

Jim

Which version of Access?
If Access 2000 or later, you can use:

SortField: Mid([YourField],InStrRev([YourField],"-")+1)

and sort on this field.

In Access 97, you'll need to write your own user defined function to
find that last hyphen and then return the numbers. It's not difficult.

 

[JethroUK©]

you could use this function:

http://jethrouk.members.beeb.net/Functions.htm#JTextPart

usage in your case would be:

JTextPart([yourfield],"-",3)