Sort problem (1 Viewer)

[Harris@Z]

Hi, hope someone can assist please!
I am struggling to do a Desc sort this query on the first field, Sec

SELECT TOP 30 Mid([DocNumber],InStrRev([DocNumber],Chr(47))+1) AS Sec, Left([DocNumber],4) AS Try, QB_Invoice.DocNumber, (Right([DocNumber],5)) AS TheDocNumber, QB_Invoice.TimeModified
FROM QB_Invoice
WHERE (((Left([DocNumber],4))>2023) AND ((QB_Invoice.DocNumber) Is Not Null));

I have tried
ORDER BY Mid([DocNumber],InStrRev([DocNumber],Chr(47))+1) DESC;
and
ORDER BY Sec DESC;

But cannot get this to work.
Any ideas please?

 

[GPGeorge]

What DOES happen? What does it mean to say it doesn't work?

Show us some examples of the values created by your expression if you can. It might help clarify what the sort is working with.

 

[Gasman]

It would appear the sort field is a string field, so will sort alphabetically?
Are you expecting to it to sort numerically?

 

[Harris@Z]

Thanks for your inputs.
The field DocNumber, contains invoice numbers.
It is a string field with values such as 2024/1543, 2024/1544, etc.
However, occasionally contains a value without the "2024/" part

I am trying to extract the 'highest' invoice number used.
So I have created a field to show the number following the forward slash, and then want to sort that for the highest value.

The error I get is "data type mismatch in criteria expression"

 

[plog]

However, occasionally contains a value without the "2024/" part

That "occasionally" is the key. When you use that word and want to translate it into code, you need some sort of conditional statement (IIF, SWITCH, WHILE, etc.). I see none in your SQL.

First though, the admonishments--the real solution to this issue is to store your data properly. 1--Discrete pieces of data need to be stored discretely. You don't jam multiple pieces of data into one field--especially "occasionally". 2024/1544 is 2 pieces of data and each needs its own field. 2024 goes into a field for the year, 1544 should go into its own field as well. 2--Data needs to be stored as the correct type. You want to sort those pieces of data numerically, but you've stored them as text, so to do so you've got to go through extra coding on top of the seperation coding you already need to do because you've jammed everything together. You've chosen a pound of medicine instead of an ounce of prevention.

Since you've got to do 2 things--conditionally divide out your data into 2 pieces and convert it to numbers, I suggest you do this slowly a step at a time. Start a new query. Bring in DocNumber so you can see what you are starting with. Then, make calculated fields that seperates out your data as you need it. This includes any fields that you will ultimately use to filter or ORDER BY--but don't filter or ORDER BY them, just have the query spit out the data in the SELECT so your eyes can verify everything is working correctly. Once every field displays correctly add the WHERE part. Once that works add the ORDER BY part.

You're trying to do a lot all at once. Do it step by step and verify as you go. Or again, better yet, fix your data.

 

[The_Doc_Man]

The field that contains a slash can ONLY be treated as a string field which means that it sorts according to text string rules, one digit at a time. When the field data stays equal length for all sub-components taken individually, the sort order is the same for text and numeric. When one of the component sub-fields is shorter than the others, the sort will appear to jump around a bit and will no longer appear consistent.

 

[Harris@Z]

I have no control over the data.
This is how it is stored in Quickbooks Online, and I am linked via ODBC to this data via my Access database.
I cannot change what has been previously inputted.

So I am trying to find a way around this.
Interestingly, if I click the sort descending in the query, it sort correctly. But with coding . . that is why I am here!

 

[Harris@Z]

Following some suggestions here, I created a query without the order by, saved it, and then did a second query sorting by the Sec field, and this appears to have worked!

 

[Gasman]

Or you could use VAL() ?

 

[Pat Hartman]

A string of varying length will never sort correctly. 100 will always be less than 99 because 1 is less than 9.

 

[Harris@Z]

Thanks for further input.
I tried Val() to no benefit.

Regarding "string of varying length", the data set I now deal with are those all preceded by "2004/" hence my condition, and all values following are 5 characters (numbers), e.g., 00001, 10001, 12345

 

[jdraw]

Show your revised code "that isn't working".

 

[Gasman]

Thanks for further input.
I tried Val() to no benefit.

Regarding "string of varying length", the data set I now deal with are those all preceded by "2004/" hence my condition, and all values following are 5 characters (numbers), e.g., 00001, 10001, 12345

Really? then I have no idea as to why.

VAL(Mid([DocNumber],InStrRev([DocNumber],Chr(47))+1))
WHERE Val( (((Left([DocNumber],4)))>2023)
 ORDER BY Val(Mid([DocNumber],InStrRev([DocNumber],Chr(47))+1)) DESC

 

[GPGeorge]

Thanks for further input.
I tried Val() to no benefit.

Regarding "string of varying length", the data set I now deal with are those all preceded by "2004/" hence my condition, and all values following are 5 characters (numbers), e.g., 00001, 10001, 12345

Unfortunately, this example demonstrates that these are not numbers, as has been pointed out in several previous posts:

00001, 10001, 12345

These are strings of 5 characters. Coincidentally, they happen to be digits, but they are, nonetheless strings and will sort as Pat, for one, has suggested.

 

[Mike Krailo]

Maybe something like this. I created some sample data as shown below.

Now with a simple query to create two more data columns called DocNumYr and DocNum, we can easily populate those coluimns of data using a couple of functions.

' Return the year data if present
Function GetDocYr(QBnum As String)
   If InStr(1, QBnum, "/") > 0 Then
      GetDocYr = CLng(Trim(Left(QBnum, 4)))
   Else
      GetDocYr = Null
   End If
End Function

' Return the DocNumber without the year data
Function GetDocNum(QBnum As String) As Long
   If InStr(1, QBnum, "/") > 0 Then
      GetDocNum = CLng(Trim(Mid(QBnum, 6)))
   Else
      GetDocNum = CLng(Trim(QBnum))
   End If
End Function

Now from this query, your can easily sort your data however you desire because the new data is numerical. See attached database sample to view the query.