MS Access compare a value to a table column and return a field from the same row

Question

Ok so i have a complex reason field from one of our logging servers, and i need to break it down to make some sense, problem is the format changes depending on the status.

I managed to find some strings that i can compare the the reason to to get some sense out of it, but I want to distill it down to one reason code.

I scratched my head a bit and got it down to 7 reasons with different criterion, put the criteria in a table and came up with some vb code to do the comparison.

Problem is its dead slow, and half the reporting relies on the Reason code. The basic VBA function is below, This basically loads the criteria into an array and then compares the value against the array to return the ID.

Function Reason_code(LongReason As String) As Integer
Dim NoReason As Integer
Dim I As Integer
Dim J As Integer
Dim x As Boolean
NoReason = recordCount("RejReason") - 1
Dim conExpr() As String
ReDim conExpr(NoReason)


For I = 0 To (NoReason - 1)
     conExpr(I) = GetVal("Criterior", "RejReason", "id", CStr(I + 1))
Next I

For J = 0 To (NoReason - 1)
    x = LongReason Like conExpr(J)
    If x = True Then
        GoTo OutOfLoop
    End If
Next J

OutOfLoop:
Reason_code = J + 1

End Function

I have used similar in VB before and it tends to be quite fast, so i am reconing that my GetVal function is the problem, but my VBA is rusty and my SQL is pretty non existent, so any help would be appreciated. I tried LSQL and SQL2 as one line but VBA doesnt like it.

Function GetVal(FieldNm As String, TableNm As String, IndexField As String, IndexNo As String) As String
 Dim db As Database
 Dim Lrs As DAO.Recordset
 Dim LSQL As String
 Dim LGST As String
 Dim SQL2 As String

 'Open connection to current Access database
 Set db = CurrentDb()

 'Create SQL statement to retrieve value from GST table
 LSQL = CStr("SELECT " + FieldNm + " FROM " + TableNm)
 SQL2 = CStr(LSQL + " WHERE " + IndexField + " = " + IndexNo)
 Set Lrs = db.OpenRecordset(SQL2, dbOpenDynaset, dbReadOnly)

 'Retrieve value if data is found
 If Lrs.EOF = False Then
    LGST = Lrs(0)
 Else
    LGST = "Item Not found"
 End If

 Lrs.Close
 Set Lrs = Nothing

 GetVal = LGST
End Function

Thanks in advance,

Answer 1

I Scratched my head for a bit and worked out i could speed it up by doing the read and compare at the same time, its not lightning, but its better

Function ReasonCode(LongReason As String) As String
Dim cdb As Database
Dim rs As DAO.Recordset
Dim RejRea()
Dim NoReason As Integer
Dim result As Boolean
Dim i As Integer

Set cdb = CurrentDb()

Set rs = cdb.OpenRecordset("RejReason", dbOpenDynaset, dbReadOnly)
rs.MoveLast
rs.MoveFirst

NoReason = rs.recordCount - 1
RejRea() = rs.GetRows(rs.recordCount)

For i = 0 To NoReason
result = LongReason Like CStr(RejRea(2, i))
If result = True Then
  ReasonCode = CStr(RejRea(1, i))
  GoTo outloop
End If
Next i

If ReasonCode = "" Then ReasonCode = "Not Found"

outloop:

Set rs = Nothing
Set cdb = Nothing
End Function

Still not sure its the best way to do it, but in the abscence of any other suggestions it will do for now.