Is there a "reverse" switch function?

[kirkm]

Switch accepts a number and returns a string but I need to pass a string and return a number.
Is there a command like switch that does this? I tried Enum but it won't accept variable names.
I have this working but can't help feeling there another way??

Function GetTheInteger(nn As String) As Integer
    'Define number for nn
    Dim Lvalue As Variant, n As Integer
    Lvalue = Array("", "One", "Two", "Three", "Four", "Five")
    For n = 1 To 5
        If Lvalue(n) = nn Then
            GetTheInteger = n
            Exit Function
        End If
    Next
End Function

 

[bastanu]

Why don't you use Switch:
MyNumber: Switch([MyField]="One",1,[MyField]="Two",2,[MyField]="Three",3,[MyField]="Four",4,[MyField]="Five",5)
Cheers,

 

[kirkm]

Cool! Thanks bastanu, guess I didn't undestand Switch function properly. Duh.

 

[pbaldy]

Another option would be to put the values in a table. That would let you join that table in queries to get the value, or use DLookup. It would be more maintainable, getting business logic out of code. What happens when you need to add 6/six?

 

[kirkm]

Good idea. Didn't think of Table/Dlookup, but I like the switch one-liner. Where used there's never 6 only 1-5.

 

[bastanu]

Glad to help!
Cheers,
Vlad