Sorting with letters

[awojciehowski]

I have a database where I label entries by the alphabet (A-Z). However, when I have more entries than the alphabet has letters I then double the letters (AA-ZZ) and so on.

I want to create a sorting code that will list all single letters then double letters, triple and so on.

I am somewhat of a novice so if anyone has some ideas I sure would appreciate it along with a bit of detail.

Thanks much!
Adam

[FishVal]

Hello, Adam.

Try something like the following:

SELECT * FROM [YourTable] ORDER BY Len([Label]), [Label];

[awojciehowski]

Sorry, I am working in MS Access and am not that familiar with VB Coding. Could you provide a bit more detail on how to complete your suggestion?

Thanks!

[FishVal]

Queries in Access using SQL View
Basic SQL Syntax for Access Queries

http://en.wikipedia.org/wiki/Select_(SQL)

What makes records to be ordered is what is written in ORDER BY clause of SQL expression:

... ORDER BY Len([Label]), [Label];

this tells database engine to sort records first by length of [Label] field, then by value of [Label] field.
I guess in query design view grid it looks like column "ExprN: Len([Label])" with ascending sorting order and "[Label]" column with ascending sorting order rightward to it.

[awojciehowski]

FishVal-

I appreciate the help but this does me no good. I have no idea what you are trying to explain, even though I have read through it a few times. I attempted to modify my query with sort example but all I got was a bunch of errors.

Anyone else wanna take a stab at helping out?

Thanks
Adam

[FishVal]

Ok, Adam.

Open your query, change View to SQL, post contents of query window.

[NeoPa]

A QueryDef (saved query in Access) has two different ways that its design can be shown in Access :

1. Design View.
2. SQL View.

When either is displayed, the other can be selected by using the View menu and selecting the relevant option. What this means is that when changes are made in either view, the changes are visible in both.

Because of this, we can get a pretty good idea of what your query is doing simply by looking at the SQL (that you can see and post here for us simply by using the SQL view). It also means that we can give you changes to the SQL that you will be able to put into the SQL view of the QueryDef, and see the intended changes in Design view.

[awojciehowski]

Thanks for the explanation...h ere is the SQL info that I was able to recover:

SELECT   Entrys.SPDCaseNumber,
         Entrys.Location,
         Entrys.LedgerNumber,
         Entrys.OfficerID,
         Entrys.ReceivedDate,
         Entrys.[EvidenceDisposed?],
         Entrys.EvidenceTypeID,
         [Entry Details].LedgerID,
         [Entry Details].EvidenceName,
         [Entry Details].Quantity,
         [Entry Details].Notes,
         [Entry Details].ItemNumber,
         Entrys.EvidenceTypeID,
         Entrys.[Warrant?],
         Entrys.CaseResolvedDate,
         Entrys.TobeDisposed

FROM     Entrys INNER JOIN [Entry Details]
  ON     Entrys.LedgerID = [Entry Details].LedgerID

ORDER BY Entrys.LedgerNumber,
         [Entry Details].ItemNumber

[NeoPa]

I'll try to look at this in more detail later, but for now I've just rearranged the code so that it's a little easier to read and digest.

[NeoPa]

I have a database where I label entries by the alphabet (A-Z). However, when I have more entries than the alphabet has letters I then double the letters (AA-ZZ) and so on.

Adam, can you say which field you're referring to here? The one where you store the alphabetic characters.

[awojciehowski]

It would be the Item Number field that I would want to be organized as A, B, C then AA, BB, CC then AAA, BBB, CCC and so on.

[NeoPa]

In that case add some spaces at the front then chop off the rightmost n characters. I worked to a potential maximum of 5 chars, but you know your data better than I.

The other change I made (setting and using the ALIASes) had no real effect, it simply makes it easier to read and work with when in SQL view.

SELECT   tE.SPDCaseNumber,
         tE.Location,
         tE.LedgerNumber,
         tE.OfficerID,
         tE.ReceivedDate,
         tE.[EvidenceDisposed?],
         tE.EvidenceTypeID,
         tED.LedgerID,
         tED.EvidenceName,
         tED.Quantity,
         tED.Notes,
         tED.ItemNumber,
         tE.EvidenceTypeID,
         tE.[Warrant?],
         tE.CaseResolvedDate,
         tE.TobeDisposed

FROM     Entrys AS tE INNER JOIN [Entry Details] AS tED
  ON     tE.LedgerID=tED.LedgerID

ORDER BY tE.LedgerNumber,
         Right('    ' & tED.ItemNumber,5)

[NeoPa]

You could also use Fish's technique (which I missed). Just replace lines #21 & #22 with :

ORDER BY tE.LedgerNumber,
         Len(tED.ItemNumber),
         tED.ItemNumber

This is actually a better, more flexible technique anyway. I'd go with this version for preference.

[awojciehowski]

NeoPa-

I copied and pasted what you suggested into my query...now when I view the record it still is not sorted correctly and when I click on a button to print the current record is comes up with an missing parameter value of "Entry Details.Evidenc e Name".

I am attaching a screen shot for you to understand what I am looking at.

[NeoPa]

That makes the problem very clear Adam. I'm afraid you haven't copied & pasted the code across correctly.

That prompt reflects something that doesn't exist in my SQL.

I suggest you try again. If it fails again, copy in the SQL from your database again exactly as you did before, and include it in your next post.