Advanced Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my database's Main table I have a field that is alpha-numeric (i.e.
CB-01-1101-09-07) The number is NOT assigned to every row, only a select
number of rows have this number assigned to them, but the numbers are
different for each one assigned.

the first letters "CB" always remain the same.
the second set of numbers "01" will always be a number between 01-09, this
represents a region designation.
the third set "1101" is the actual "Number" assigned to the row and will
increase by 1 for each new number assigned.
the fourth and fifth numbers represent the Month and year respectively "09-07"

My question is... I would like to create a query (if possible) to sort this
column only by the second set of numbers "1101" so that I can see if any
numbers are out of order and need to be fixed. I've attempted several
different queries but I'm not versed well enough (yet) in access.
 
You should get a different field for each basic information, ie, one field
for the date, one for the region, one for the sequential number.

Else, you can sort on an expression (can be slow):

MID( fieldName, 7, 4)


assuming there is always 6 characters before the sequence of (always) 4
characters to be sorted.


Else, you can always use a complex expression to locate the delimiter (-)
and to extract the desired value.




Hoping it may help,
Vanderghast, Access MVP
 
thank you for replying Michel,

first off I'm unable to seperate the data, so creating a seperate field for
each is out of the question (I thought of that, but the form I use to input
the information would take too much time to redo, along with the 1000's of
entries already in the db, I was hoping for a much simpler approach)


you mentioned "sort on an expression" could you elaborate on that? Where is
the expression placed in the query?
 
You type the expression in the first line of the query designer, and you
'sort' that computed column. Type


MID( fieldName, 7, 4)


(with the real fieldName) as expression to be so sorted.



Hoping it may help,
Vanderghast, Access MVP
 
In the query design view, go to the right till you find an empty column.

Put "uniqueID: mid([alphanumericID],7,4)" in the first row (but without the
outside delimiting double-quotes!).

Use the pulldown to make the 'sort' row say 'ascending' (or 'decending' if
you'd rather)

Run the query. The 'mid(FieldName,Start,Chars)' stuff is the 'expression'
and you're sorting on the basis of the values that result from that
expression. If you don't care about that value for any reason but the sort,
you can un-check the 'show' box for that column

That's what Michel meant by "sort on an expression"

Hope that helps!
jk
 
Thank you Michel, that is going to work out just fine, it's not exactly what
I had in mind because it just shows the "CB-09-XXXX-09-07" the XXXX part of
the field, but I added the CBID column so that I can look through and find my
errors.

Thanks for your help.
 
Back
Top