Sort order Text, Numerical "Both ?"

[IanS]

In my SELECT query I'm using ORDER BY to sort the data in my recordset.

The field we're sorting on is a text field but it often contains numerical data.

As I'm sure you know this results in numbers being sorted like 1, 10, 100, 2 ,3 etc (In text sort "100" comes before "2". We all understand that so no need to explain in more detail)

But what I'd like to be able to do is - Have all the rows containing ONLY numbers sorted numerically 1, 2, 3, 10, 100 etc and then, either at the start or end of the recordset, have all the rows containing any text sorted as text.

I suppose the best way would have been to design this in from the start and have the data stored in two separate columns in the database - A number column and a text column. But it's an old database, lots of users and I'm really looking for some way to improve the sorting on the existing data.

Any ideas ?

Thanks
Ian

Edit - I forgot to say - This is currently in an access 2000 database - all code is in a vb6 application - there is no code, forms, queries, macros etc in the database. The database is just somewhere to store data. All logic must be in the application.

[si_the_geek]

Which database system are you using?

[IanS]

Sorry, I just edited that data into my previous post.

Thanks
IAn

[IanS]

Sorry, I also forgot to say (in case it's relevant) All reading and writing to the database is done using SQL - I never use data objects, bound grids or anything like that.

I also use DAO (please don't tell me to use ADO - I don't have any problem using ado. I do lots of work with ado - but the argument about which is better/faster against jet databases is a different topic )

[si_the_geek]

This is currently in an access 2000 database

In that case try this:

SELECT <fields>
FROM <table>
WHERE IsNumeric(<fieldtosortby>) = True
ORDER BY CLng(<fieldtosortby>)

UNION ALL

SELECT <fields>
FROM <table>
WHERE IsNumeric(<fieldtosortby>) = False
ORDER BY <fieldtosortby>

Sorry, I also forgot to say (in case it's relevant) All reading and writing to the database is done using SQL - I never use data objects, bound grids or anything like that.

Good

I also use DAO (please don't tell me to use ADO - I don't have any problem using ado. I do lots of work with ado - but the argument about which is better/faster against jet databases is a different topic )

Which is faster is irrelevant, DAO died many years ago (even the VB6 help [written in 1998] says not to use it for any new projects), and it now fails in many situations - such as 64-bit versions of Windows.

ADO is still current.

[IanS]

Thank you for those code suggestions - I'll take a look.

Re: DAO

and it now fails in many situations - such as 64-bit versions of Windows.

So far my application appears to work just fine in 64bit windows - I did some quick testing myself when 64bit windows was first released and since then haven't tested it specifically on 64 bit. My application is shareware so I suppose it's possible that new trial users hitting a problem would simply go away and try something else but there's a lot of existing users many of which will have upgraded to 64 bit versions of Vista and Win7 and so far none of them have come shouting at me.

Is there any information about dao/64-bit compatibility, how to reproduce problems etc ?

[si_the_geek]

My application is shareware so I suppose it's possible that new trial users hitting a problem would simply go away and try something else

Not just "possible", almost certain.

but there's a lot of existing users many of which will have upgraded to 64 bit versions of Vista and Win7 and so far none of them have come shouting at me.

Not being told is far from a guarantee, the problems may not occur in all situations (it may only be certain features of your app, or depend on other software that is installed, etc), and even those that do have issues may not tell you - they may be willing to live the issue(s) they get (and swear about you in their head, and to their friends), or stop using the program and/or spend money with your competitors.

Is there any information about dao/64-bit compatibility, how to reproduce problems etc ?

The latest "new" information about DAO I've seen from a reliable source (rather than a newbie, or somebody re-posting existing tutorials) is this page from 2002:
http://msdn.microsoft.com/en-us/libr...map_old_topic9
...which includes DAO in the "Obsolete" section, and uses phrases like:

"Do not use these technologies when you write new applications."
"It will not be available on the 64-bit Windows operating system."

The last one (added to the page a few years ago) is very heavy hint that even Microsoft themselves can't get it to work reliably - because they include the VB6 runtimes with 64-bit versions of Windows.

So far my application appears to work just fine in 64bit windows

O-kay.... I'm not sure how that's a good reason to be using a tool that was out of date when VB6 was released way back in 1998.

[IanS]

I'm not sure how that's a good reason to be using a tool that was out of date when VB6 was released way back in 1998

I suppose we could also ask why anyone would still use VB6 but any time of day or night there's as many people using the vb6 area of this forum as there are using the .net area. Most of time there's more people browsing the vb6 forum showing that 'old technologies' are still very popular - I reckon that's because, used correctly, they work very well.

Actually - I'd be very interested to know how many people are still using older DB technologies. Could you set up a Poll asking people to select between ADO, DAO, RDO etc etc. Maybe people could select more than one option - Would that be an interesting poll ?

[si_the_geek]

I suppose we could also ask why anyone would still use VB6

There are many reasons, several of them valid, and I still use it myself.

VB6 is old (only superseded in 2002) but it is still officially supported to some degree, and I am not aware of a time frame for that ending.

DAO (a VB ?4? technology) is far from comparable, because it was out of support before VB.Net was released - it has been obsolete for many years (even Windows 2000 isn't in that state yet).

Most of time there's more people browsing the vb6 forum

That depends heavily on your timing... I usually see the opposite. It is also far from reliable for a variety of reasons, such as people counting as still doing their last action for up to 15 minutes.

The site admins have told the moderators a few times over the years that the VB6 forum has occasional peaks, but if you look at the total (in terms of the actual accurate info, such as page loads) for an entire day or more, the VB.Net forum gets noticeably more action - which is why the VB.Net forum moved above the VB6 forum a few years back.

Actually - I'd be very interested to know how many people are still using older DB technologies. Could you set up a Poll asking people to select between ADO, DAO, RDO etc etc. Maybe people could select more than one option

You can set it up yourself - its one of the options when you make a new thread.

Would that be an interesting poll ?

I don't think so... but I'm not the one trying to pretend that moving up as far as a 1997 technology is a bad idea in 2010.