Re: SQL Text and Number sort

Hi,

I think you can convert the SQL to VB. You would have to loop through the
data (a recordset) and create the new field to sort on. Or better yet,
create the new field as you read the data from it's source into a recordset.
In rough outline:

If IsNumeric(Field1) Then
NewField = Right("00000" & Trim(Field1), 5)
Else
NewField = Field1
End If

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Patch61" <Patch61@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FE00AECA-EF77-4253-A7C0-C16DA5E3E036@xxxxxxxxxxxxxxxx
Thanks, but I need to do this in MS Jet/VB6.


"Stephen Howe" wrote:

I have a field I am sorting on that is a text field which includes
numbers.
Is there any way to sort this field so that, for example, 2356 comes
before
12685 while maintaining alphabetical order for the true text entries?

I can do it using SQL Server using a hack


SELECT field1
FROM table1
ORDER BY
CASE ISNUMERIC(field1)
WHEN 0 THEN field1
WHEN 1 THEN RIGHT('0000' + LTRIM(RTRIM(field1)), 4)
END


field1 is a 4 character field.
I am distinguishing numeric from non-numeric and adding leading 0's to
the
numeric so that is sorts properly.

Stephen Howe





.

Relevant Pages

  • Re: sorting by multiple fields in dao recordset
    ... > is the syntax for sorting by field1 then by field2? ... I really doubt that you want to "sort the recordsets... ... There are multiple options of which two are: Base your recordset on a ...
    (microsoft.public.access.modulesdaovba)
  • RE: Looping through a Recordset
    ... When you SET your recordset, perhaps you can use some simple SQL to get the recordset data to sort the same as the table.... ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)