Question about sorting

[ErinL]

Hello -

This question seems like it should have an easy answer but I have not been able to find it.

I have a field called LineNumber that is a Long Integer. When I sort on this field, it sorts like this:
1
10
11
2
3
4
5
6
7
8
9

How do I get it to sort like this:
1
2
3
4
5
6
7
8
9
10
11

The number is automatically generated with code so it has to stay numeric in type.

Thank you in advance.

 

[pbaldy]

You may see it as numeric but Access is seeing it as text (that's alphanumeric sorting). How is the number generated? Something in that process is causing it to be interpreted as text.

 

[ErinL]

Thank you for the quick reply Paul!

Here is the code for generating the line number:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter or select a record in the main form first."
Else
strWhere = "[OrderNumber] = " & Me.Parent![OrderNumber]
Me.[LineNumber] = Nz(DMax("LineNumber", "tblOrderDetails", strWhere), 0) + 1
End If
End Sub

And I see what you mean because (and I'm very new a writing code) it looks like the Dim strWhere As String is what is converting it to text. Is that correct? If so, how would I get around that? The code works perfect as it is to generate the line number.

Thanks again!

 

[pbaldy]

No, that's only being used for the criteria, so it shouldn't affect this. What is the data type of that field (LineNumber) in the table? Can you post the db, or a representative sample?

 

[ErinL]

The field's data type is Number and the field size is Long Integer.

 

[pbaldy]

A numeric field shouldn't do that, so again, can you post the db, or a representative sample?

 

[ErinL]

I found a fix. I created a "Line" field with the following:

Line: Val([Line#])

This worked perfect and it sorts the way I wanted it to now.

Thanks again for your help, Paul. I would not have known what to look for if you hadn't told me that it was sorting "alphanumeric".

 

[pbaldy]

Happy to help. I'd like to have figured out the cause. I've never had to do that to a numeric field, unless some intervening process had caused it to be seen as text. The Format() function will do that, and the Nz() or IIf() functions can do it. Anyway, glad you found a workaround.