Form not sorting numerically correct

[specialk1116]

I have a form that lists evidence items 1, 2, 3, ect....

The form is listing them 1, 10, 11, 12, 13, 2, 3, 4, 5...

I have tried right clicking the evidence item # field and doing a sort a to z, and it is still listing them the wrong way.

 

[pbaldy]

It sounds like your field is text, or a function has been used to cause it to be seen as text. First option is changing the data type of the field.

 

[specialk1116]

I need it to be a text field because we sometimes have evidence items that need to be listed as 1A.

Is there any other suggestions.

Thank you so much for your help again. You helped me yesterday.

 

[apr pillai]

The data is treated as text in sorting. Check the field data type on the Table, if it is text change it to Number.

 

[apr pillai]

The only solution that I can think of now is to create a SELECT Query with an extra column with the expression numEvidence:Val([evidence]), and place it on the Form, in addition to the actual field. But, this will serve the purpose of sorting only.

 

[missinglinq]

And that's still not going to take into account the Alpha suffixes the OP has indicated they sometimes have. I'd use two Fields:

Field1: Defined as a Number Field for the numeric part of the evidence ID

Field2: Defined as Text to hold the Alpha suffix that the ID sometimes has

You could then do a Primary Sort on the Numeric Field and a Secondary Sort in the Suffix Field.

This would give you something like

1
2
3A
3B
4
5
6
7
8
9
10
11

Linq ;0)>

 

[Brianwarnock]

And then there is this method

http://www.access-programmers.co.uk/forums/showthread.php?t=120275

Brian

 

[missinglinq]

...isnumeric thinks 2D1 is numeric...

That's because 2D1 is a number... in Hex!

...Val("2D1") returns 20...

That would apparently be the Value of 2D1!

The IsNumeric() Function is problematic, as you've seen! It will return True for strings that have A-F in them if they can represent Hex numbers! It will also return True if a string has a single Decimal Point, leading Minus, Plus or Dollar Signs, Parens, if they surround around digits, i.e. (66), and probably others I haven't tested for!

Workarounds abound, out of necessity, and are always (to me) interesting to work out, but I usually use the simple expediency of only allowing digits to be entered:

Private Sub YourTextBox_KeyDown(KeyCode As Integer, Shift As Integer)

 Select Case KeyCode
  
  Case 48 To 57
   'Numerical characters (digits) are allowed
  
  Case vbKeyDelete, vbKeyBack, vbKeyReturn, vbKeyRight, vbKeyLeft, vbKeyTab
   'Allow these navigation/function keys to be used
  
  Case vbKeyNumpad0, vbKeyNumpad1, vbKeyNumpad2, vbKeyNumpad3, vbKeyNumpad4, vbKeyNumpad5, vbKeyNumpad6, vbKeyNumpad7, vbKeyNumpad8, vbKeyNumpad9
   'Allow input from Numbers Keypad

  Case Else
   'Don't allow any other keys to be used
   KeyCode = 0
 End Select

End Sub

Depending on the user group, I sometimes include a Messagebox if a user attempts to enter an illegal character, sometimes don't! Depends on my mood!

Linq ;0)>

 

[ChrisO]

Sub TestIt()

    [color=green]' Except when the argument is Null, the Val() function returns a Double:[/color]
    MsgBox TypeName(Val(Empty))             ' << Double
    MsgBox TypeName(Val(vbNullString))      ' << Double
    MsgBox TypeName(Val(""))                ' << Double
    MsgBox TypeName(Val("0"))               ' << Double
    MsgBox TypeName(Val("1"))               ' << Double
    MsgBox TypeName(Val("Fred"))            ' << Double
    MsgBox TypeName(Val("1Fred"))           ' << Double

    [color=green]' E means scientific notation: Number * (10 raised to +/- Exponent).
    ' D means the same except, in some languages, D also means force to Double.
    ' Since the Val() function returns a Double, D is equivalent to E in VBA.[/color]
    MsgBox Val("2E1")                       ' << 20
    MsgBox 2 * (10 ^ 1)                     ' << 20

    MsgBox Val("2D1")                       ' << 20
    MsgBox 2 * (10 ^ 1)                     ' << 20

    MsgBox Val("2E-1")                      ' << 0.2
    MsgBox 2 * (10 ^ -1)                    ' << 0.2

    MsgBox Val("2D-1")                      ' << 0.2
    MsgBox 2 * (10 ^ -1)                    ' << 0.2

    MsgBox Val("2E307")                     ' << 2E+307
    MsgBox 2 * (10 ^ 307)                   ' << 2E+307

    MsgBox Val("2D307")                     ' << 2E+307
    MsgBox 2 * (10 ^ 307)                   ' << 2E+307

    MsgBox Val("2E308")                     ' << Overflow
    MsgBox 2 * (10 ^ 308)                   ' << Overflow

    MsgBox Val("2D308")                     ' << Overflow
    MsgBox 2 * (10 ^ 308)                   ' << Overflow


    [color=green]' Hex has nothing to do with it. For Hex to be interpreted as such
    ' it would need the prefix of &H. That then allows the raising of
    ' the base to 16 depending on position; least significant to the right.[/color]
    MsgBox Val("&H2D1")                     ' << 721
    MsgBox (2 * 256) + (13 * 16) + (1 * 1)  ' << 721

End Sub

Chris.

 

[ChrisO]

Missinglinq.

Would you please stop bolding so much in your posts?
All it is doing is drawing attention to your mistakes.

Chris.