Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting oddly named cells in Visual Basic. 4

Status
Not open for further replies.

tjtindal

Programmer
Mar 22, 2004
66
US
I had to think for a minute as to what I was trying to say when I was writing the subject line, but hopefully this'll clear it up:

What's the easiest way to force sorting in a particular order when you have odd entries like this?

111ss
112ss
113ss
11ss <- this should be above 111ss
121ss
122ss
123ss
12ss <- this should be above 121ss

I know the quick answer: put a "0" zero in front of all the smaller numbers to equalize it. Unfortunately, that's just not an option because it's "11ss", not "011ss".

My only idea is to create another field in the db for sorting, a numerical field like 1, 2, 3, 4, etc. so it'll be forced to sort in that order, but the tricky part is not having to rewrite the entire table everytime sorting is changed for all the entries under the changed number. Like, if there's 500 entries, and I change number 5, I don't want to have to rewrite all the fields for 6-500. Know what I mean?

Does anyone have any thoughts as to another method of sorting?

 
>but situations like this one where the numeric part always appears at the start of the string are quite legitimate places to use VAL.

It has nothing necessarily to do with a certain thread or VB book to show how it can cause problems, and the VB help files are sometimes misleading and even wrong, leading unexpected users into a possible pitfall.

I had to dig out some old references.
My old MS Basic Version 7.0 reference 1989 shows VAL()s usage similar, to VB, but in my opinion a little better.
It states:
Val Function: "Returns the numeric value of a string of digits"
"The argument stringexpression$ is a sequence of characters that can be interpeted as a numeric value"
The rest is basically the same as VB Help, even the Street number example.
"Val: to convert between numbers and strings"

And an MBasic/Basic-80 book from Kent Porter 1984:
"...be careful with VAL() to make sure nonnumeric characters don't appear in the string. Unpredictable results ensure if they do."
"..other Basics crash the program or otherwise wreak havoc."

Anyways, the VB help does not mention this.
At least on my non-US/English system/version it can produce wrong results (sorry, I haven't had access to anything different for a long long time)

VB help does mention the fact that it can interpet radix prefixes. This may lead to wrong results if VAL is used other than already.
What it doesn't mention is what happens when it encounters numerics with exponents (again, on my system).

So, take the VB examples and please try them to see if they produce different results than I have, which is not working as mentioned.

(original)
?Val(" 1615 198th Street N.E.")
?Val("24 and 57") ' Returns 24.
?Val("2457")

I'll change them a little to read:

?Val(" 1615 d198th Street N.E.")
?Val(" 1615d-5 198th Street N.E.")
?Val("1615d-5 198th Street N.E.")

?Val(" 1615 d-5 First Street N.E.")
(House #1615 apt. d-5 First street N.E.)

?Val("2457xyz") 'Ok
?Val("2456e11")
?Val("2456d1sff")
?Val("2455d100abc")
?Val("24 e10 57")

Please giive these a try and see what the order of sort they should fall into and where they really end up.

Maybe they show up right somewhere on your system, but never did for me.

I think it's best to use VAL only as stated, unless you are sure that the remaining charachters are not going to get in the way.
 
Kinda long?? You could probably have shortened it a little. No matter!

I took you data and imported it into an access database. I named the table Table1 and the field is Field1, so you'll have to change those to be your table and field.

Notice the Order By. This is where the magic happens.

Code:
Select Field1  
From   Table1 
Order By IsNumeric(Left(Trim(Field1), 1)) DESC, 
       iif(IsNumeric(Left(Trim(Field1), 1)) = -1, 
           Right('0000000000' + Field1, 10), 
           Field1)

The pseudo-code...

order by 'whether or not' the first character is numeric.
Secondary sort: if the first character is numeric, then order by Right('0000000000' + Field1, 10), otherwise, jsut order by the field.

Also notice that I have a couple trim's in here, which you may be able to remove, and I order by IsNumeric DESC so that 'text' shows at the top and 'numbers' show at the bottom.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
My response was for the Original Poster. In no way do I wish to enter the, "Proper use of val" debate. Please don't mis-interpret any of my comments as such.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
[blue]gmmastros[/blue], I haven't tested the following, but maybe it's worth checking out:

How does Access vba function (Jet I mean, in an Sql statement, not an Access module or the Query window-except the Sql window) Val() interpet/handle numeric strings with exponents symbols?

Also, with IsNumeric is the same problems as with Val with something like Numeric("2456d9") ?

though with the Numeric it may be more harmless because you cannot use any other characters.

I suspect they work the same, all maybe using same vba module.

Note: Has nothing to do with IsNumeric in your code, (I believe using the RIGHT function is the best way) as you are only checking the first left character, so there is no problem with that.
Just curious. It's not really a debate on the proper use, but more what the results may be. Maybe I am wrong now, or at least my system, but as far as I know the Val function works the same as it always did.

If you don't know the answer then it's ok. I'll test it some other day.
 
>> For "properly" calculating numeric strings with-out having to worry what numeric type it needs to be converted to.

If I am not mistaken, the val function returns a specific data type, which happens to be double. Any assignment of the val function to anything that is not double would end in an implicit conversion.

If was intended only for numeric strings, then why would it accept something else? If you know you are passing only numeric strings to convert, then wouldn't it be better to use the CDbl function instead? I assume it does much less checking of the contents of what you send it than what Val does.
 
SammyB

Please note, from a previous post...

Me said:
In no way do I wish to enter the, "Proper use of val" debate.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
I think I'd better just clarify my position: I simply disagree with this statement

>You shouldn't really use the VAL() function on anything but strings which contain only numeric data, which is what it was intended for

(and using documentation from an older, different language to try and justify the unsubstantiated claim seems ... odd)
 
George, unfortunately this code isn't doing anything for me:

Select Field1
From Table1
Order By IsNumeric(Left(Trim(Field1), 1)) DESC,
iif(IsNumeric(Left(Trim(Field1), 1)) = -1,
Right('0000000000' + Field1, 10),
Field1)

"iif" ? Isn't that a Quickbooks import/export file extension? How can I use iif in a SQL string?
 
Golom, your code, however:

.RecordSource = "SELECT * FROM Lots ORDER BY Val(Lot), Lot"

WORKS PERFECTLY! This has answered my question completely. Thanks alot EVERYONE!
 
From VB6 Help:

IIf Function
Returns one of two parts, depending on the evaluation of an expression.

Syntax:
IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.
 
IIF is a very powerful command. It exists in VB and also Access's implementation of SQL.

Just for fun (and a learning exercise), pop open a 'fresh vb project. Add a command button, and this code.

Code:
Option Explicit

Private Sub Command1_Click()
    
    Debug.Print IIf(1 = 1, "Must be true", "Must Be false")
    Debug.Print IIf(1 = 0, "Must be true", "Must Be false")
    
End Sub

I think this demonstrates the workings of IIF, but you can look it up in the help files to learn more.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Also, if you've ever used Excel's =IF function or Lotus's @IF function, it's exactly the same thing.

Bob
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top