Sorting numbers in Access queries

Topic

New Reply

This is simply a question as to whether there is a better way to sort a field than the way we’ve worked with it in an expression. Here’s the scenerio, we bring in a numeric field from an external database source it has six characters, but it can have a leading zero. The leading zero is dropped so in a sort the order is not correct. We’ve gotten around this by adding an expression that includes &Format([SeqNum], “000000”) but we aren’t sure that is the easiest way.

Is there a way that we could change the number every time the number is imported so that any sort that is done thereafter is correct. This is one of our users’ database, and he’s now able to work with the data as he’s added the expression, but in my own mind I’m wondering why can’t the field be correctly formatted on import so that no matter what is done with the number it will sort correctly. My understanding is that the number is not used in calculations.

Your comments and suggestions are most welcome,

Reply | Quote

Replies

It appears you must be importing the data into an Access field that is a text field rather than numeric. Otherwise the data should sort correctly regardless of how you format it for display purposes. Or have I missed something obvious?:huh:

Reply | Quote

Hi! It sounds like the source data is actually in a character format rather than number. You could do a conversion from character to number and store the result in the target field. That way, the data in the target will sort correctly. The conversion only happens at import time. The fact that the data in the source has a leading zero probably means it is formatted as text, probably in a fixed width field.

Regards,
Kirk

Reply | Quote

The source data field must be a text field and if the field in MS Access containing the imported data, drops any leading zero, it is a numeric field.

Change the Access field to text.

It is a common misunderstanding that an all digit field should be considered numeric. US and Australian zip/post codes are all digits but you never do numeric operations on them, similarly telephone numbers. These are text data and in all my systems are treated as such.

Reply | Quote

You can also use the expression Val(SeqNum) in the query and sort on that. That gets the numeric value of the field. As mentioned in a number of other replies it definitely appears that the field is being treated as a character field and not a numeric value.

Reply | Quote