Access sorting and grouping

[Cia2a]

I have a field in access with data like this<br>99-acct-23<br>99-acct-2<br>99-syst-23<br>12<br>23<br>98-syst-33<br>How do I sort this so the 99s are first and alpha numeric records follow in correct order. THe descending does not work.<br><br>2. I have a query that says I have too many expressions in grour by clause. What can I do to solve this?<br>Thanks in advance

 

[elizabeth]

Can't really answer this as you don't explain in suffiecient detail, such as where you would want th 98 records to show up. But you can experiment with Access's string functions to maipulate the string so that it looks to Access like something it can sort as you'd like. Create these Expressions in one or more columns in your query and use them to sort on. You can uncheck the display checkbox if you don't want those expressions to be displayed in the resulting query. Here's a couple of ideas:<br><br>Expression1: Mid(YourFieldName, 3,10)<br>Expression2: Left (YourFieldNameYourfieldName,3)<br><br>If your scheme is complex enough that even by stripping off the year indicator (I'm guessing that's what the 98 & 99 are), you may have to use the criteria section of the QBE to only look at ceratin values. Finally, if you need to take one action in one case, but a different action in another, you would need to use the Iif function.<br><br>An alternative is to &quot;clean up&quot; the data, if it all belongs n the same format, by adding a year indicator to the beginning of the fileds without it. For that ou would use an update query. For instance if you wanted all records that did not have a year indicator to begin with a year 97 indicator, you could concatenate it in an expression like this:<br>&quot;97-&quot; & YourControlName<br><br>Good luck. If you get stuck again maybe try rephrasing your question with a little more detail and reposting in the &quot;Microsoft: Queries and SQL&quot; forum on this site.