Sort on field with numbers entered as text

[RobertLund]

I have a text field which number the records, and need to sort as if the numbers are actually numbers. If I sort now, I get 1,11,2,21, etc. because the values are text equivalents of numbers. Is there a function, as in Excel’s =Value(text) that I can use?

 

[Ranman256]

in the query, add another field to convert the text field: Val([field])
sort on that field.

 

[jdraw]

Try sort on CInt(yourfieldname) Where yourfieldname IS NOT NULL. Let us know how it goes.
Better to show us the query sql.

 

[theDBguy]

Is there a function, as in Excel’s =Value(text) that I can use?

Hi. Welcome to AWF!

As @Ranman256 said, you can use the Val() function, but an additional field/column is not required. If you're using the query designer, you can uncheck the Show box. Otherwise, you would use something like.

SELECT ... ORDER BY Val(TextField)

Cheers!

 

[Pat Hartman]

If the field is always an integer, why not change the data type?

 

[arnelgp]

Val() will not work for Null values, so you need to fix the code:

ORDER BY Val([fieldname] & "")

 

[RobertLund]

I have a text field which number the records, and need to sort as if the numbers are actually numbers. If I sort now, I get 1,11,2,21, etc. because the values are text equivalents of numbers. Is there a function, as in Excel’s =Value(text) that I can use?

thank you everyone for your help with this. I do have NULL record values to eliminate in the table, so this is the query:

SELECT tblSheetMusic.FirstHitsNumber, tblSheetMusic.Title
FROM tblSheetMusic
WHERE (((tblSheetMusic.FirstHitsNumber) Is Not Null))
ORDER BY Val(tblSheetMusic.FirstHitsNumber);

This does create an unchecked column, and it works fine.

Many thanks for all of your inputs.
Cheers,
Robert

 

[theDBguy]

thank you everyone for your help with this. I do have NULL record values to eliminate in the table, so this is the query:

SELECT tblSheetMusic.FirstHitsNumber, tblSheetMusic.Title
FROM tblSheetMusic
WHERE (((tblSheetMusic.FirstHitsNumber) Is Not Null))
ORDER BY Val(tblSheetMusic.FirstHitsNumber);

This does create an unchecked column, and it works fine.

Many thanks for all of your inputs.
Cheers,
Robert

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[RobertLund]

If the field is always an integer, why not change the data type?

I did try that at first, and it worked. But It’s a linked excel table that is not under my control, and I just need to use it as a read only source of information.