Sorting of Listbox

[poporacer]

I have a listbox that I need sorted and I am not sure how the best way would be. The problem is the format of the data that I need to sort. The data is in the form AAA-AAA-YY-MM-1234 where AAA is misc. letters, YY is the year, MM is the month, and 1234 is an identifying number. I need to sort the info by YY-MM-1234, with the year as the first sort order then MM as the second sort order and then 1234 as the final sort order. I figured I could use string manipulation to remove the first part of the number, store the info in a table and then query that table. When I use this # I will need to access the data stored in the original table. To do this, I would have to do some more string manipulation to find the matching record. Can anyone think of a query that can do this without the conversion/reconversion?
Thanks for your help

 

[gemma-the-husky]

is the info in one string, or in multiple fields

....
in any event base the listbox on a stored query, and add extra columns in your query to build the sort order (you can have columns in the query to do this, that arent in cluded in the list box)

 

[poporacer]

The info is in one string. How do I split the string into columns in the listbox?

 

[doco]

If I understand correctly:

The data is already in a table and in AAA-AAA-YY-MM-#### format
You have stripped the first eight characters from the string
Leaving YY-MM-####

If you have a field in your table with that parsed string AND
the length of YY is always two IE 01 - 99 AND
the length of MM is always two IE 01 - 12 AND
the length of #### is always four IE 0000 - 9999 THEN
set the sort order of that field to ASC and it will be sorted by Year then Month then the number string. That field then could be placed in your list box and sorted correctly.

But something tells me I am missing something here - enlighten me...

 

[poporacer]

I guess I wasn't clear...The data is stored as AAA-AAA-YY-MM-#### format (With or without the dashes, I can work around the dases) I have not stripped the first characters, it is stored in the entire format. I have a feeling that I am going to need to store each section in a different field, and then do a bunch of string manipulation...hopefully there is a better method.

 

[DCrake]

Simple Software Solutions

You will I assume have a query that is sourced by the list box... In this query add a further column called Sorted.

Sorted:Right(Field,n)

Where Field is the name of the field and n is the number of characters from the end of the contents that will give you the chunk of string you need.

The sort this column either Ascending or Descending, depending on your choice.

Reset the column count to take this new column into consideration and make the column width 0cm (hidden)

CodeMaster:

 

[doco]

You will I assume have a query that is sourced by the list box... In this query add a further column called Sorted.

Sorted:Right(Field,n)

Where Field is the name of the field and n is the number of characters from the end of the contents that will give you the chunk of string you need.

The sort this column either Ascending or Descending, depending on your choice.

Reset the column count to take this new column into consideration and make the column width 0cm (hidden)

CodeMaster:

Which is kind of what I said

 

[poporacer]

I am not sure how to do what you suggest.... the list box source is a query

SELECT DISTINCT tblOffense.[Log#]
FROM tblOffense;
I am not sure how to add a column to a query.. A query is just a filter isn't it? I tried

SELECT DISTINCT tblOffense.[Log#]
FROM tblOffense, Sorted:Right(Log#,6);

But got an error....any suggestions?

 

[doco]

SELECT
      Log#   --  BTW. you should never put '#' in a column name
    , Right( Log#, 6 ) as trunc_log  --  create new column
FROM
    tblOffense
ORDER BY 2 asc

 

[poporacer]

Thanks....Worked GREAT!!!! I changed the field to LogNum