Solved Term as a Value

TKaisierBHG

New member
Local time
Today, 08:34
Joined
Jul 3, 2023
Messages
8
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?
 
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.
 
Hi
You would need an If statement.

If (NameofControlthatContainsWord]="Easy",1,If (NameofControlthatContainsWord]="Medium",2,""))
 
I would also vote for the table approach.
 
To associate a word with a value you can also use a dictionary or a collection, like...

Code:
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
 
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

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
 
Sorry for the non helpful reply, I appreciate this, I will try to implement what you said and see if it works
 

Users who are viewing this thread

Back
Top Bottom