I am still managing a floor plan database for various offices across Canada. All has been well, until one office decided to go against a standard I put forward for office numbering, and wanted to include letters in the seat numbers. So for instance, they have office/desk numbers like 3D01 and 3C123.
because my standard would be 3-000, where the first digit is the floor, and the latter digits being to denote the desk number (the minus seperates floor number from desk number so entries like 10-01 and 1-001 would be more easily understood), I could easily re-order the listing of desks by parsing the string to remove the minus, and sorting by the whole number. That way the listing is sorted by numerical order, instead of the auto number.
That aside, to do this I was running a query function like:
reOrder: Val(Replace([OfficeNumber],"-",""))
unfortunately, for this office with the letters in the seating, I get the value of "3"(for something like 3C15) or 3E+15 (for both 3E15 and 3D15).
My big problem is that I cannot just drop the letter, since I have 3C5, 3D5 and 3E5, which are not near each other. I'm guessing I now need a test to see if there is a Char in the string, but I am not sure how to do that. I am guessing that I need an IIF to test for this then output either the Val or just a straight string... can anyone help with this?
Thanks.
because my standard would be 3-000, where the first digit is the floor, and the latter digits being to denote the desk number (the minus seperates floor number from desk number so entries like 10-01 and 1-001 would be more easily understood), I could easily re-order the listing of desks by parsing the string to remove the minus, and sorting by the whole number. That way the listing is sorted by numerical order, instead of the auto number.
That aside, to do this I was running a query function like:
reOrder: Val(Replace([OfficeNumber],"-",""))
unfortunately, for this office with the letters in the seating, I get the value of "3"(for something like 3C15) or 3E+15 (for both 3E15 and 3D15).
My big problem is that I cannot just drop the letter, since I have 3C5, 3D5 and 3E5, which are not near each other. I'm guessing I now need a test to see if there is a Char in the string, but I am not sure how to do that. I am guessing that I need an IIF to test for this then output either the Val or just a straight string... can anyone help with this?
Thanks.