Ordering a string numerically

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

Guest

I have a string that's like "0101A" and "515A". When these strings are
ordered (in the Order By clause) the "0101A" comes before the "515A". In
other words it's being ordered alphabetically. I need it to be ordered
NUMERICALLY! 515 comes before 1010. How can I do this in a VB6 program
reading from an Access database?

The query looks like this:

STR_TEMP = "SELECT LABELS.BIN, LABELS.ROUTE, LABELS.STOP, " & _
"FROM LABELS " & _
"WHERE (((LABELS.PRINT_TYPE) = """ & "STORE" & """)) " & _
"ORDER BY LABELS.ROUTE, LABELS.STOP DESC, LABELS.BIN;"

Thanks

Steve
 
Steve said:
I have a string that's like "0101A" and "515A". When these strings are
ordered (in the Order By clause) the "0101A" comes before the "515A". In
other words it's being ordered alphabetically. I need it to be ordered
NUMERICALLY! 515 comes before 1010. How can I do this in a VB6 program
reading from an Access database?

The query looks like this:

STR_TEMP = "SELECT LABELS.BIN, LABELS.ROUTE, LABELS.STOP, " & _
"FROM LABELS " & _
"WHERE (((LABELS.PRINT_TYPE) = """ & "STORE" & """)) " & _
"ORDER BY LABELS.ROUTE, LABELS.STOP DESC, LABELS.BIN;"

Thanks

Steve
Click to expand...

You could try wrapping VAL() around whichever text field(s) you want
sorted numerically in the ORDER BY clause:

ORDER BY VAL(LABELS.ROUTE), VAL(LABELS.STOP) DESC, VAL(LABELS.BIN)
 
I have a string that's like "0101A" and "515A". When these strings are
ordered (in the Order By clause) the "0101A" comes before the "515A". In
other words it's being ordered alphabetically. I need it to be ordered
NUMERICALLY! 515 comes before 1010. How can I do this in a VB6 program
reading from an Access database?
Click to expand...

Use the Val() function to extract the numeric value of the leading
digits of the string:

STR_TEMP = "SELECT LABELS.BIN, LABELS.ROUTE, LABELS.STOP, " & _
"FROM LABELS " & _
"WHERE (((LABELS.PRINT_TYPE) = """ & "STORE" & """)) " & _
"ORDER BY LABELS.ROUTE, Val(LABELS.[STOP DESC]), LABELS.BIN;"

Note also that since the field STOP DESC contains a blank (which is
probably a bad idea), you must enclose the fieldname in square
brackets. I'm assuming that this is the field which needs sorting, you
don't say.

John W. Vinson[MVP]
 
John said:
I have a string that's like "0101A" and "515A". When these strings are
ordered (in the Order By clause) the "0101A" comes before the "515A". In
other words it's being ordered alphabetically. I need it to be ordered
NUMERICALLY! 515 comes before 1010. How can I do this in a VB6 program
reading from an Access database?
Click to expand...

Use the Val() function to extract the numeric value of the leading
digits of the string:

STR_TEMP = "SELECT LABELS.BIN, LABELS.ROUTE, LABELS.STOP, " & _
"FROM LABELS " & _
"WHERE (((LABELS.PRINT_TYPE) = """ & "STORE" & """)) " & _
"ORDER BY LABELS.ROUTE, Val(LABELS.[STOP DESC]), LABELS.BIN;"

Note also that since the field STOP DESC contains a blank (which is
probably a bad idea), you must enclose the fieldname in square
brackets. I'm assuming that this is the field which needs sorting, you
don't say.

John W. Vinson[MVP]
Click to expand...

I think the OP's "LABELS.STOP" is the table & field name, and DESC is
the sort order, so bracketing [STOP DESC] would not be quite right...
 
I think the OP's "LABELS.STOP" is the table & field name, and DESC is
the sort order, so bracketing [STOP DESC] would not be quite right...
Click to expand...

<BLUSH>

Ooopsss... you're quite correct of course.

John W. Vinson[MVP]
 
Back
Top