Sorting text field into numeric values

[acnovice]

Hi,

I have QNos in table tblShop, data type is text
1
1A
1B
2
2A

from my query, I want to sort QNos like

2
2A
1
1A
1B

I tried ORDER BY Val(tblShop.QNo), tblShop.QNo DESC;
but it tells me "Data type mismatch in criteria expression."

I need some help.

 

[CautionMP]

acnovice,
Here is one thought.

SELECT [Qnos]
FROM [i]YourTable[/i]
ORDER BY Left([Qnos],1) DESC , Right([Qnos],1);

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[formerTexan]

IF the numeric part of QNos gets past 9, you will need to take an extra step.

Place the following public function in a module and then reference the function in a query field as shown below. Set the sort order and you are in business.

QNum: pfExtractNum([QNos])

'adapted from a Dev Ashish function
'************ Code Start **********
Public Function pfExtractNum(ByVal strInString As String) As String
Dim lngLen As Long
Dim strOut As String
Dim i As Long
Dim strTmp As String
    lngLen = Len(strInString)
    strOut = vbNullString
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
            strOut = strOut & strTmp
        End If
    Next i
    If IsNumeric(strOut) Then
    pfExtractNum = CLng(strOut)
    End If
End Function

Cheers,
Bill

 

[acnovice]

CautionMP,

Following is my real QNos and it sorted base on your suggestion.

989
959
544
110
989A
989B

This is not what I want.
It would be
989
989A
989B
959
.....

Thank you.

 

[acnovice]

Hi formerTexan,

Can you please tell me how to or where to put "reference the function in a query field" ?

It generates an another error when I put the reference in my query.

I appreciate your answer.

 

[Golom]

Could be because you have NULLs in the field

ORDER BY Val([red]NZ([/red]tblShop.QNo[red],"0")[/red]) DESC, tblShop.QNo DESC

 

[CautionMP]

acnovice,
[tt]ORDER BY IIf(IsNumeric(Right([qnos],1)),[Qnos],Left([Qnos],Len([qnos])-1)) DESC , IIf(IsNumeric(Right([qnos],1)),1,Right([Qnos],1));[/tt]?

This gave me:[tt]
Qnos
989
989A
989B
959
544
110[/tt]

Hope this helps,
CMP

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[NvrSaDi]

Not sure that I follow the former Texan, being a former Texan myself, but assuming that the appended letters do not wrap to AA, BB, etc, I would convert the letters to decimals and append to get values
989.1
989.2
989.3
Then sort, adding a second field with numeric IDS, and convert the decimal back to a letter. The numeric IDs would then serve as a sort order.

 

[acnovice]

Hi Golom,

It works well.
However, how do I know there are NULLs in the field. I can't say there's NULLs visually.

Thank you.

 

[acnovice]

Hi CautionMP,

That works great.
Thank you.

 

[Golom]

A NULL will appear as an empty field in the table.

You really don't need to know that there are NULLs but you should usually employ defensive coding and allow for the possibility.

If a field does not contain NULL then the NZ function will just return the value in the field. If it is NULL then NZ will return the default value ("0" in this example.)

 

[acnovice]

Golom,

It's good to know that.
I appreciate your reply.
Have a good day.

 

[formerTexan]

Howdy everyone,

Sorry I was on a hiatus, but for the edification of former Texans and non-former Texans, the function works perfectly well when used in a query in my corner of the Access universe.

SELECT pfExtractNum([QNos]) AS Qnum
FROM tblTABLE
ORDER BY pfExtractNum([QNos]);

Nulls are a valid issue, but the function is easily amended:

Function pfExtractNum(ByVal varIn As Variant) As Variant
Dim lngLen As Long
Dim strOut As String
Dim i As Long
Dim strTmp As String

If Len(varIn) > 0 Then
    lngLen = Len(varIn)
    strOut = vbNullString
    For i = 1 To lngLen
        strTmp = Left$(varIn, 1)
        varIn = Right$(varIn, lngLen - i)
        If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then
            strOut = strOut & strTmp
        End If
    Next i
    If IsNumeric(strOut) Then
    pfExtractNum = CLng(strOut)
    End If
Else
    pfExtractNum = varIn
End If

Cheers,
Bill