
Real Vs Binary
Now I'm trying the advance people. Don't let me down. I have a text field that I wish to order by ascending . The field contains a combination of digits and periods, this field determines where the record belongs in the hierarchy of the information that I am reporting on.<BR>Problem: I can not treat this field as numeric as in many cases it contains more than one decimal point (period) BUT.... when I try to order by the number, it does not do so as real numbers Below is a comparison of what I get to what I want.<BR>Any suggestions??? Please help me...<BR>What I want____________________ What I get<BR>1______________________________ 1<BR>1.1____________________________ 1.1<BR>1.1.1__________________________ 1.1.1<BR>1.1.2__________________________ 1.1.10<BR>1.1.3__________________________ 1.1.11<BR>1.1.4__________________________ 1.1.2<BR>1.1.5__________________________ 1.1.3<BR>1.1.6__________________________ 1.1.4<BR>1.1.7__________________________ 1.1.5<BR>

RE: Real Vs Binary
If you want to keep the current layout :<BR> you could justify using leadings 0. For example sorting 01.01.01 and 01.01.10 gives the good result...<BR> you could extract and sort subparts.<BR><BR>I would consider changing the design. 3 integer fields such as Level1, Level2, Level3 seems more appropriate to me :<BR> you want to deal with integer numbers, not alphabetical order where "10" is lower than "2"<BR> it's generally bad to store several information in the same field as it's much easier to concatenate information rather than to split...<BR>

RE: Real Vs Binary
The problem being...if there are a thousand workstations, which each require their own CI # then I have to put three trailing zeros on everything and what if that number increases. As for you suggestion to change the design I have to use HP's model and there is specific meaning to each number used in the CI#. Thanks for your help. Any other things I could try?

RE: Real Vs Binary
What's in fact are those numbers for ? If this is just to establish some kind of relation ship you could just have a unique identifier for each item and a parent/child table that would contain the relationships. IMO it will be definitely difficult to store multiple informations in a unique field...

RE: Real Vs Binary
Or.... you could store the information as text, eg 1.4.12 etc, but in the database table also have a 'real numeric value' field, where you combine the individual columns in the 3 part identifier to get a unique numeric. This can then be used in the SQL OrderBy to get the desired result. It will also as a result be quick in Queries etc esp if you make it a key field.<BR><BR>If you have 10 column1's, 100 column2's and 1000 column3's, you can make the unique identifier by eg<BR>1.4.12 = 104012<BR>8.40.997 = 840997<BR>etc<BR>

RE: Real Vs Binary
What happens if I have the following 1.1.1.12 and 1.11.1.2 are they not the same number then? I also thought of replacing the "." with zeros but I run into this, 1.10.1 and 101.1 they are both stored in the database as 10101. It is an identification number for a company. They represent the position of each item in the Configuration hierarchy. It's very similar to "Process Data Modeling".<BR><BR>

RE: Real Vs Binary
1.1.1.12 produces 1010112<BR>1.11.1.2 produces 1110102<BR>This is assuming the lefthand 1 is only ever 19, and all other sets of digits are 1 to 99. So valid numbers would be<BR>2.23.45.1 or 3.99.1.1 or 1.1.22.99<BR>producing<BR>2234501, 3990101, 1012299<BR><BR>You have to decide the largest number of digits in each 'column' and pad to the left with zero's. This generates a unique number that will sort on value.<BR><BR>You can of course change the rules for the number of digits, so long as it is decided in advance (this is the only downside to this method)<BR><BR><BR><BR>

RE: Real Vs Binary
I'm sorry but I can't figured out how you got those numbers. Could you spell it out for me please.

RE: Real Vs Binary
This is VB. You may have to tweak it for VBScript, or convert to DLL, etc.<BR>txtInput and txtOutput are forms in a VB program, but can be strings in your script. The string rep of 1.99.10 or whatever goes in txtInput, and the result "19910" appears in txtOutput. You will have to fiddle with the padding if your format is other than n.nn.nn<BR>The resulting string should convert to a number and will sort in your database. Also, this number can be converted back again so there is no need to store a stringrep in your database of the original n.nn.nn format (unless it starts with a zero i.e. 0.11.22 in which case you will have to apply your brain a bit.)<BR><BR><BR> Dim idx As Integer<BR> Dim oldidx As Integer<BR> Dim str As String<BR> <BR> Dim iNumOne As Integer<BR> Dim iNumTwo As Integer<BR> Dim iNumThree As Integer<BR> <BR> Dim strNumOne As String<BR> Dim strNumTwo As String<BR> Dim strNumThree As String<BR> <BR> Dim bError As Boolean<BR> <BR> bError = False<BR> <BR> ' extract the three digits<BR> idx = InStr(txtInput, ".")<BR> If idx > 0 Then<BR> str = Left(txtInput, idx  1)<BR> iNumOne = Val(str)<BR> strNumOne = str<BR> Else<BR> bError = True<BR> End If<BR> <BR> oldidx = idx<BR><BR> idx = InStr(idx + 1, txtInput, ".")<BR> If idx > 0 Then<BR> str = Mid(txtInput, oldidx + 1, idx  oldidx  1)<BR> iNumTwo = Val(str)<BR> strNumTwo = str<BR> Else<BR> bError = True<BR> End If<BR> <BR> If idx > 0 Then<BR> str = Right(txtInput, Len(txtInput)  idx)<BR> strNumThree = str<BR> iNumThree = Val(str)<BR> Else<BR> bError = True<BR> End If<BR> <BR> If bError = False Then<BR> ' rules:<BR> ' num one is only ever 19<BR> ' nums two and three are 099<BR> If iNumOne < 1 Or iNumOne > 9 Then bError = True<BR> If iNumTwo < 0 Or iNumTwo > 99 Then bError = True<BR> If iNumThree < 0 Or iNumThree > 99 Then bError = True<BR> If bError = True Then<BR> MsgBox "Format is n.nn.nn  get it right!"<BR> Else<BR> ' now pad the two numbers with zeros<BR> If iNumTwo <= 9 Then<BR> strNumTwo = "0" & strNumTwo<BR> End If<BR> <BR> If iNumThree <= 9 Then<BR> strNumThree = "0" & strNumThree<BR> End If<BR> <BR> ' now build the result<BR> str = strNumOne & strNumTwo & strNumThree<BR> txtOutput = str<BR> <BR> End If<BR> Else<BR> MsgBox "Not a valid number to parse (missing numerals or not 2 dp)"<BR> End If

RE: Real Vs Binary
Thanks Gizz that helped. Only a couple of things that could stop this from working. The left most side could get a large as the right and the bottom level is unknown. It can get very, very large. ie. 34.43.54.23.54.43.1.234 ect. Thanks again, Chad
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

