Creating a complex query using the "LEN" function.

[cavery]

Hello All,
Question for anyone out there. I have 5 tables in my database. One of the tables is titled "Whole Number". The Whole Number table is formatted as such: 60650SOX A001H1. The Whole Number could also come in groups and look like this A002, A003, A004, even A0011, A0012 etc. I would like to create a query using the LEN function to only retrieve the last number in that group. Example if the numbers came in groups like: 60650SOX A001H1, 60650SOX A002H1, 60650SOX A003H1, I only want to retrieve the last Whole Number which is 60650SOX A003H1. Or if the numbers came in groups like: 60650SOX A0011H1,60650SOX A0012H1,60650SOX A0013H1, I still would like the last Whole number. I want to place the LEN function or whatever works...in the Criteria field in Query Design View, since I dont use alot of VBA.

Sorry for any confusion, let me know if you want more explanations....Thanks.

Clark
Honda of America

 

[TomCologne]

Go to

http://www.mvps.org/access/strings/index.html

and check the 2 articles on top of the list. There is some assembly required for your needs, but the first article's functions are probably very close to your needs and can easily be adjusted. I've bookmarked the site.

You're not alone,

TomCologne