Unanswered: Performing comparisons with operators held in variables
I've managed to paint myself into a corner in a database. I have a series of tables that define periodic and irregular KPIs that are recorded for a website. I also have a series of forms that I use to add new records.
Having got bored ticking a box to indicate whether a KPI passed or not, I decided to create another table to hold the passing criteria (check ID, criteria description, trigger (score value - numeric) and comparison (=, <, >, etc).
I have coded everything to deal with maintaining the above. I can work out how to pull the trigger and comparison operator into variables. I cannot figure out how to use those variables to determine if the value passes or not.
How do I build a comparison statement with my comparison operator in a variable?
Function IsSuccess(lngID as Long, dblScore as Double) As Variant
Dim varData as Variant
Dim dblTrig as Double
Dim strComp as String
Dim strSQL as String
varData = DLookup("[Data]", "vwSuccessCheck", "[CheckNo] = " & lngID)
If IsNull(varData) Then
IsSuccess = Null
dblTrig = CDbl(FINDWORD(varData, 5, "~")
strComp = FINDWORD(varData, 4, "~")
IsSuccess = dblScore strComp dblTrig
10% of magic is knowing something that no-one else does. The rest is misdirection.