I've got an access database which has text fields containing (mostly) numbers e.g.

165
234x412
56x5
1000

and also some blank entries.

The problem is that I want to sort the query by these fields numerically so that:

1000 > 200
and 100x45 > 90x15

(string sort returns "200" > "1000" and "90x15" > "100x45", of course.)

I'm doing ORDER BY CInt(fieldname)

But it dies on any fields with the "x" in (i.e. not pure numeric):

'Data type mismatch in criteria expression'

Also, in any recordsets containing blank entries in the field I am sorting on, I get this error:

'Invalid use of Null'

CInt seems rather unforgiving. Are there alternatives I can use in SQL queries which will assign zero to a null text string, and fail gracefully when non-numeric characters are present?

Or can I add a custom conversion routine (how?)

e.g. IdealConversion("145x56") returns 145

Or is reworking the database (split nnnxmmm into two separate numeric fields, add zeros in all blank fields) the only way forward.

I notice DavidR said in another thread:

-------------------------------------------
Depending on your database design, it seems to me you'd be better off storing alpha, numeric, and date data in separate fields with appropriate data types. You're probably going to find it MUCH more usable in the future (I speak from the voice of painful experience here).
-------------------------------------------

but I really need a short term bodge. http://www.access-programmers.co.uk/ubb/frown.gif

The fact is that the entries containing the x's are really two pieces of discrete information and as such should be stored in separate fields. In any event, the Val() function will return the leading numeric characters from a string field which should help you to sort properly.

Thanks, that worked great (I had to add zeros in the empty fields and logic to display empty table cells instead of '0'.)

It's true those fields should be separate. That will take some extra logic in the display code but in the long run it sounds better.

In the Wrox press Access forum there's some stuff about custom comparison functions, coded in VB not VBscript though.

A test function I wrote didn't work when called in an SQL query on an active server page instead of Val() - there's probably some technique to this that I haven't figured yet.

I can't say for certain but I would guess that you would only be able to use standard SQL (and possibly VBA) functions when the query is run outside of an Access db.