Sort challenge, I'm wondering if there is a better way to accomplish a requirement.
The requirement is to present a list of connections by termination cabinet, panel strip and junction point. The data is all stored in an Access table and I retrieve it with a simple query.
Select CableID, Cabinet, Panel, Junction from Connections order by Cabinet, Panel, Junction
The issue is that the fields are strings which means they sort like this:
Rather than like this
Temporarily I altered the Cabinet slightly adding a leading 0 to the string which results in a successful output.
The problem is that the data is synchronized with another source and I can't change the value in the source. So when the sync routine runs it identifies each of the changed values as a conflict.
I was hoping I could use the format function but it only works with pure numeric data.
If Cabinet = "123" or 123 then Format([Cabinet],"0000") returns 0123 but
If Cabinet = "1M" then Format([Cabinet],"0000") returns 1M not 001M as I was hoping.
I thought about parsing off the last character i.e. Left([Cabinet],len([Cabine
t])-1) but Cabinet is not a consistent size.
1,1M and 1ML are all valid Cabinet designations.
I also thought about adding the 0 to the front i.e. "0"&[Cabinet] but I have the same issue 1M and 1ML would need the 0 but 11M would not and 1ML and 11M are the same length.
So I'm looking for other ideas,