length Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.
I'm going to step into this ONLY as an ASIDE, not saying you should actually do this, though I CAN offer a solution. And DON'T take this as any form, however slight, of chastisement. I'm a teacher by heart and see this as a useful learning experience, so take this in the intended tone.
The reason you ran into difficulties is a design-level issue. If you have a mixed field like XXnn-nnn to work with because that is the way you originally get the data, I can understand why you DIDN'T do what I'm going to suggest. And the solution I will actually propose is based an important but simple fact: Queries are your friends. They do all the hard work for you, if you let them - and ANYTHING you can do using a table as a recordsource can be done using a QUERY as a recordsource.
The essence of good database design is to decide ahead of time what you were planning to do with a given data element when you got it. Looking at it from the "design ahead" concept (as opposed to the "design to play catch-up" method), you would have caught that there was a time you would have needed the field's parts to be handled separately.
Method (A) would be to break apart the field and store the information separately when you first get it, then build a query that recombines it when you need it to be together. In that case, the DMAX operation would have been trivial.
Method (B) would be to write a query that separates the elements of your complex field into its separate parts so you can work with them separately anyway. For instance, if the first part of the field is ALWAYS XXnn and there is ALWAYS a dash, you COULD do something like this...
Code:
SELECT a, b, c, LEFT( YearNumber, 2 ) AS YrNumPrefix, MID (YearNumber, 2, 2 ) AS YrNumNumber, MID( YearNumber, 6 ) as YrNumSequence, ... FROM tblData WHERE Len( YearNumber ) > 0 ;
As pointed out by MajP the 3rd argument in MID would default to the remainder of the string. So I used that fact for the field I called YrNumSequence. And note that I'm skipping the dash because you don't need it for what you are doing with the DMAX.
Once you have THAT query in place, you can do a DMAX of the domain represented by the query (NOT that of the base table) and work with the fields that way - because anything you can do with a table can usually be done through a query instead.
Since retrofitting would be a royal pain in the toches, I suggest consideration of plan (B) to get you going forward right now. But the problem stems from originally failing to consider what you needed to do with the data in question.
So there are two main lessons here:
1. Designing a good database includes trying to look ahead of time to see how you need to work with the elements you have. If you know what you need to do before you do it, your life becomes a LOT simpler because you can make better-informed decisions at an earlier time. (And can REJECT solutions that will be pains in the patootie.)
2. Queries are your friends and can help you divide a problem into parts such that your end goal becomes easier to write. (This is a "divide and conquer" strategy.)
Thanks for the very thorough and thoughtful response. No offense taken whatsoever. I will take all the help I can get.
You are 100% correct and in the end, I ended up splitting up the data and using queries as you and Colin suggested. I've been testing it most of the day today and it seems solid.
