Row Source Error Checking

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

Guest

Ok since the last question was so easily answered I thought I might try
another. This could get adicitive...

Here's the deal, I have a list box that I am filling with an SQL statment
from VBA so I can use the users input from another list box to get the right
data in this one. The problem is the field that contains the data I want to
put in this list box has some text and some integers in it. I want to be
able to try to sort the data like it is numbers first and if that does not
work have it swich to a second SQL where I remove the CSNG function and sort
it like text.

Man that is a long explaination. Here is what is happening now on some
selections of the first list box it is going fine, while on others I am
getting a Data Type Mismatch error as it trys to execute the CSNG. Is there
a way that I can trap that error and have it go to a sub or function that
replaces the SQL in the row source.
 
Hi,


The ORDER BY clause can handle many columns. In this case,

IsNumeric( string ) returns true (-1) or false (0) if the string can
represent a value

Val( string ) returns the number "up to" illegal character are met, or 0 if
start with an illegal character,


? IsNumeric("567"), IsNumeric("567g"), val("567"), val("567g"), val("hello")
True False 567
567 0



and since, with Jet, true = -1 (when generated as result), and false = 0,


ORDER BY IsNumeric(myField), val(myField), myField


will order the all numerical fields first, then among the non-all-numeric,
accordingly to their returned result from val, and then, if there are still
ex-equality, accordingly to the original value int he field.


You may also consider:

ORDER BY val(myField), myField




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top