Strings to Exponent values (1 Viewer)

inlanoche

Registered User.
Local time
Today, 04:50
Joined
May 31, 2013
Messages
13
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Is the first character/digit still represent the floor? If so, then maybe you just simply use the field as a sort field without using Val(). Have you tried that? Just curious...
 

Micron

AWF VIP
Local time
Today, 07:50
Joined
Oct 20, 2018
Messages
3,476
Val stops at the first character that is not recognized as a number. Find the letter then concatenate what comes before and after the letter? Not sure because you didn't post an example of what you want for a result, and there is no dash in 3C123, so why try to remove one? Another oddity that you've noticed is that Access can interpret E as scientific notation, thus the weird results. Lastly, if your data is text, you should probably forget about trying to sort it because 123 comes before 2.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Jan 23, 2006
Messages
15,364
for consideration:
Here's a procedure to adjust the alpha to meet your "specs" for processing.


Code:
Sub Desks()

    Dim x As String, v As Variant, i As Integer, j As Integer
    x = "3D01,3C123,14E2,7e14" 'sample floor/desks from new guy
    v = Split(x, ",")
    For i = LBound(v) To UBound(v)
    Debug.Print v(i) & " original"
        For j = 1 To Len(v(i))
            If Mid(v(i), j, 1) Like "[A-Z]*" Then
                Mid(v(i), j, 1) = "-"
                Exit For
            End If
        Next j
           Debug.Print vbTab & v(i) & " adjusted "
      Next i
End Sub

Output:

Code:
3D01 original
    3-01 adjusted 
3C123 original
    3-123 adjusted 
14E2 original
    14-2 adjusted 
7e14 original
    7-14 adjusted
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:50
Joined
May 21, 2018
Messages
8,463
A little mod because 3A01 and 3B01 cannot be the same. Maybe you can replace A with 1 and B with 2. This works fine up to J because 3J01 becomes is 31001 looking like it is on the 31st floor.

Code:
Public Function RoomSort(OfficeNumber As String) As Long
 Dim ltrVal
 Dim I As Integer
 If InStr(OfficeNumber, "-") > 0 Then
   RoomSort = Val(Replace(OfficeNumber, "-", ""))
 Else
   For I = 65 To 90
     If InStr(OfficeNumber, Chr(I)) > 0 Then
       RoomSort = Val(Replace(OfficeNumber, Chr(I), CStr(I - 64)))
       Exit For
     End If
   Next I
 End If
End Function
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Jan 23, 2006
Messages
15,364
What exactly does the alpha character represent? I was not dealing with the meaning of the alpha, just replacing it with "-" to meet your spec. I need to account for the mening of the alpha.
 

inlanoche

Registered User.
Local time
Today, 04:50
Joined
May 31, 2013
Messages
13
Thank you for the posts... Just to clarify, my system was created to use the format: Floor-Desknumber
Unfortunately, this one office, does not want to use that, and won't change it. So now I am stuck with parsing office numbers with letters mid string. Not desirable, but our Facilities team is ok with this, so now I am stuck. I guess a sample of what I want the order to be like (and there is more to make it messier) is something like:
3D1
3D2
...
3D25
...
3C1
3C2
...
3C25

What makes it more complex is that they have 3C1, 3C01 and 3C001. Yes I don't understand why they think that is easy to track properly...

To top things off, they now have ask that some rooms be split, so I would have 3C1A and 3C1B as well....

in my ideal world, we set up X-## for rooms (as there should never be more than 100 close rooms on a floor), and X-### for cubes, as a floor should not have more than 1000 cubicles, were X is the floor number. Life was easy back then.... I just got back on line and will look more closely at the solutions presented, but I guess worse case senario, I would like to have all the Cs, Ds and Es together. Right now it's putting the Ds and Es together with scientific notation. Also since this is more of a universal database, I cannot separate out this office's data, so the revised order number/string should still work for offices that use the proper structure...

Thanks.
 

inlanoche

Registered User.
Local time
Today, 04:50
Joined
May 31, 2013
Messages
13
What exactly does the alpha character represent? I was not dealing with the meaning of the alpha, just replacing it with "-" to meet your spec. I need to account for the mening of the alpha.


The alpha in these cases represents a section of the floor, I believe. The trailing alpha (A-L) represents the fact that they have added/split areas and have no more numbers that they want to use. In one section they have just passed to me, they added about 12 sit/stand desks in a wide walkway. So now these are 3D148A-L.... Fun stuff...

Sorry that I was not very clear on the first post. I need to keep all the letters, or at least order the same 2nd letters together. I'm thinking the easiest way would just be to read them as a string, but with the current code I have, if I leave it as a string, it takes Scientific notation.... :(

Maybe I just leave it as is in all cases, as a string? this seems to be getting over complicated now....
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Jan 23, 2006
Messages
15,364
I think we have all experienced some degree of what you are facing. There are standards and then there are enforced standards or discipline. It would appear that data or information management in your organization is not a concern or not a priority or "the sh$$ hasn't hit the fan yet". There comes a time in the evolution of an organization (make that viable organization) where the importance of data hits home. Duplication of effort, loss of customers, inability to bill or collect from customers......get management's attention. If you are going to have a standard way to identify floors and desks etc, then all floors participate. If it's " you can opt in or out as you wish" that's chaos not standards -- BUT it's a management issue not technical. Technically you can create a method for every floor or combination, and as long as management will fund the maintenance, it's "standard (not)" -- but a management decision.

See this for the meaning of each part of VIN.

Enough soapbox --but I'm sure most of us have been there. I was for sure.
 
Last edited:

inlanoche

Registered User.
Local time
Today, 04:50
Joined
May 31, 2013
Messages
13
That is True jdraw. the database was a pet project I created when we moved offices to better track staff, seating, phone numbers, and data wiring. Because they liked it so much at my office, they have started to roll it out across Canada (where I am from). Our company spans the globe, and this is by no means a set standard, though I tried to create a system that is both functional and easy to understand. Our office had something similar to start (### for offices, #### for cubes, with the first number being the floor). I made changes so that things would not get mixed up in case floor 1 and 10 were both in use. Not likely, but one of our offices has floors 1,2,7 and 8.

The function was defined in a query column name, so I didn't want to get too complex. Looks like that may not be an option.... I think I will shift it all to text, and let it fall as it may. Really, Only someone who is setting up the office cells in the Visio part (Visio pulls the info from the main SQL server) would really see the mess. All other database lookups can be either entered or scrolled through.

If anyone else has any ideas, I'm all ears.... :)

Thanks anyway.
 

Users who are viewing this thread

Top Bottom