If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Row Source Error Checking
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 "mcgj" wrote in message ... 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. |
#2
|
|||
|
|||
Dude that is AWESOME!!!! This will solve my problems!!
Thanks for explaining that to me! John "Michel Walsh" wrote: 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 "mcgj" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reference multiple cells in if statement | PAR | Worksheet Functions | 1 | June 10th, 2005 06:28 AM |
Word cannot find its data source | Augie Dawg | Mailmerge | 1 | May 27th, 2005 10:50 AM |
Mail Merge with separate Source Header document | KM Wagner | Mailmerge | 1 | May 25th, 2005 10:23 AM |
How do I open htm in source view in Word 2003 | JP | General Discussion | 2 | May 17th, 2005 07:29 AM |
multiple docs, one data source | kp | Mailmerge | 12 | January 31st, 2005 04:41 PM |