Sorting Alphanumeric Data in a Form

[BSA]

I am wondering if there is a way to sort this data in the order I would like. I have attached a picture if the current way it sorts but I would like it to go 2W20,2W30,2W35,2W40,2W45,2W50 and so on. Is there a way to do this?

[andy49]

Guess this must be a query for cbobox. Maybe create an expression field in the query to return ending: right([fieldname], Len[fieldname]-3)

This would return 20,200,30,300,35 etc

You could then order your combobox on this field.

Sent from my iPhone using Tapatalk

[ranman256]

In a select query of your data, make 2 extra fields, a string fld for the text part of the sort :
SortText:left([field],2)

And another numeric field,SortNum: cLng(mid(field,3))
now sort the data on these 2 fields. The numeric fld will sort correctly.

[andy49]

Snap


Sent from my iPhone using Tapatalk

[Micron]

maybe, but it looks like somewhere in that list is a value that starts with 15-. Not everything appears to begin with a digit followed by one letter.
clng(Mid("15-3w120",3)) will generate an error. If you can't add a sort field to your table, you'll probably need a more robust code solution. I guess that depends on what the rest of your records look like.

[andy49]

Oops missed the 225L. If there are going to be other letters at the then I agree. A user defined function to extract the left and right ends which can be used in the query of the combobox to sort it seems to be the way forward


Sent from my iPhone using Tapatalk

[Missinglinq]

...maybe, but it looks like somewhere in that list is a value that starts with 15-.

Isn't that a different field than the one being sorted?

Linq ;0)>