Solved Term as a Value

[TKaisierBHG]

Hi Everyone, I had great help with the last post I made, thanks to everyone that jumped in.

I am hoping to represent a word as a value (i.e. easy=1, medium-2 e.t.c.) how would I go about doing this?

 

[The_Doc_Man]

In what context? In VBA there is such a thing as the ENUM declaration, which can be public or private. Using SQL, the PREFERRED method is to have a short table that lists your word value and equivalent numeric code, then use a JOIN query whenever you need to translate the code into text. AVOID LIKE THE PLAGUE any hint of using a multi-valued field (in a table) since they are nothing but trouble the moment you have to start using them in ANYTHING complex. In forms, however, there are so many ways to skin this cat that should have no real trouble.

 

[mike60smart]

Hi
You would need an If statement.

If (NameofControlthatContainsWord]="Easy",1,If (NameofControlthatContainsWord]="Medium",2,""))

 

[TKaisierBHG]

Hi
You would need an If statement.

If (NameofControlthatContainsWord]="Easy",1,If (NameofControlthatContainsWord]="Medium",2,""))

Goated with the sauce

 

[Gasman]

Hi
You would need an If statement.

If (NameofControlthatContainsWord]="Easy",1,If (NameofControlthatContainsWord]="Medium",2,""))

Or could use the Switch function?

Switch function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

though that is more typing I would go the table route.

 

[theDBguy]

I would also vote for the table approach.

 

[MarkK]

To associate a word with a value you can also use a dictionary or a collection, like...

Private c_ As VBA.Collection

Private Property Get WordScoreCollection() As VBA.Collection
    If c_ Is Nothing Then
        Set c_ = New VBA.Collection
        c_.Add 1, "Easy"
        c_.Add 2, "Medium"
        c_.Add 4, "Hard"
        c_.Add 99, "Insane"
    End If
    Set WordScoreCollection = c_
End Property

Function GetScore(Word As String) As Integer
On Error Resume Next
    GetScore = WordScoreCollection(Word)
End Function

 

[CJ_London]

in what context is this code? in a query, vba?

lots of issues:

• if this is in a query you need to use the iif function (not if)
• no square brackets before 'Finding,...'
• use of double quotes around Finding...] which are not required
• your construct implies you should be using the iif function
• repeats of values e.g. "Findinglevel]"="High",3 and "FindingCategory]"="ModelTesting",3
• repeat of If("FindingCategory]"="ModelTesting",3)

and you would have got all sorts of error messages, telling us what they are would be much more helpful than 'why isn't this running?'

see these links

IIf function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Using If...Then...Else statements (VBA)

Office VBA reference topic

learn.microsoft.com

Personally I would use a table then your code could be something like

dlookup("Code","myTable","Word ='" & FindingLevel & "'")

or you would just join to the table in a query

 

[TKaisierBHG]

Sorry for the non helpful reply, I appreciate this, I will try to implement what you said and see if it works