Real Vs Binary

Results 1 to 10 of 10

Thread: Real Vs Binary

  1. #1
    Chad Franklin Guest

    Default Real Vs Binary

    Now I&#039m trying the advance people. Don&#039t 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>

  2. #2
    Patrice Scribe Guest

    Default 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&#039s generally bad to store several information in the same field as it&#039s much easier to concatenate information rather than to split...<BR>

  3. #3
    Chad Franklin Guest

    Default 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&#039s model and there is specific meaning to each number used in the CI#. Thanks for your help. Any other things I could try?

  4. #4
    Patrice Scribe Guest

    Default RE: Real Vs Binary

    What&#039s 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...


  5. #5
    Gizz Guest

    Default 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 &#039real numeric value&#039 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&#039s, 100 column2&#039s and 1000 column3&#039s, you can make the unique identifier by eg<BR>1.4.12 = 104012<BR>8.40.997 = 840997<BR>etc<BR>

  6. #6
    Chad Franklin Guest

    Default 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&#039s very similar to "Process Data Modeling".<BR><BR>

  7. #7
    Gizz Guest

    Default RE: Real Vs Binary

    1.1.1.12 produces 1010112<BR>1.11.1.2 produces 1110102<BR>This is assuming the left-hand 1 is only ever 1-9, 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 &#039column&#039 and pad to the left with zero&#039s. 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 down-side to this method)<BR><BR><BR><BR>

  8. #8
    Chad Franklin Guest

    Default RE: Real Vs Binary

    I&#039m sorry but I can&#039t figured out how you got those numbers. Could you spell it out for me please.

  9. #9
    Gizz Guest

    Default 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> &#039 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> &#039 rules:<BR> &#039 num one is only ever 1-9<BR> &#039 nums two and three are 0-99<BR> If iNumOne &#060; 1 Or iNumOne > 9 Then bError = True<BR> If iNumTwo &#060; 0 Or iNumTwo > 99 Then bError = True<BR> If iNumThree &#060; 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> &#039 now pad the two numbers with zeros<BR> If iNumTwo &#060;= 9 Then<BR> strNumTwo = "0" & strNumTwo<BR> End If<BR> <BR> If iNumThree &#060;= 9 Then<BR> strNumThree = "0" & strNumThree<BR> End If<BR> <BR> &#039 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

  10. #10
    Chad Franklin Guest

    Default 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
  •