CODEWITHSUNDEEP
ms-accessvbams-access-2007
user1921704
user1921704

Reputation: 179

function to lookup text field in access 2007

i have the following function is used to check my text field and if it contains some words it will show up the equivalent words from a lookup table named "joinlookupall"

Function speclook(ByVal Text)
   If IsNull(Text) Then
     speclook = Null
     Exit Function
End If

Dim Data As LookupData
Static joinlookupall As VBA.Collection

If joinlookupall Is Nothing Then
   Set joinlookupall = New VBA.Collection
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("joinlookupall", dbOpenForwardOnly)

    While Not rs.EOF
    Set Data = New LookupData
    If IsNull(rs(1)) Then
    'do nothing
    Else
     Data.Key = "*" + rs(1) + "*"
    End If

    If IsNull(rs(2)) Then
     'do nothing 
    Else
    Data.value = rs(2)
    End If

    joinlookupall.Add Data
     rs.MoveNext

    Wend

 End If


  Dim S As String

 For Each Data In joinlookupall
   If Text Like Data.Key Then
     If Len(S) = 0 Then
       S = Data.value
     Else
       S = S + "," + Data.value
     End If
   End If
Next

 If Len(S) = 0 Then speclook = Null Else speclook = S
 End Function

my text is a job announcement that contains some positions required for a work , something like " a company is looking for an Electrical Engineer ......" , the function will check the job announcement to see what is the required career to show up the suitable department from a lookup table

         words (rs(1))         |   department (rs(2))
  -------------------------------------------------
      electrical engineer     |    electric-dep
      electrical engineering  |    electric-dep
      mechanical engineers    |    mechanic-dep

so in the previous example the function will show up "electric-dep"

my problem with this function is that it always check my text with only one template which is 

     Data.Key = "*" + rs(1) + "*"

i want to use many templates to check my text with it something like :

 Data.Key =  rs(1) + "*"

 Data.Key =  "*" + rs(1) 

 Data.Key =  "*" + chr(32) + rs(1) + chr(32) + "*"

so if any of this templates is exist the function will work correctly because now it doesn't work with all conditions and sometimes the words exist but it cant see it because it uses only one template or form

i have tried to use a variables for (Data.Key) but it didn't work , i want to know the correct way to make this templates work together if the function found any of it , it will work and show up the correct value

by the way to make this function work you have to create a class module named "LookupData" and put the following code in it

Public Key As String
Public value As String

Upvotes: 0

Views: 185

Answers (1)

John Bingham
John Bingham

Reputation: 2006

Why not do something like this:

Function speclook(byval text, template as string)
    ...
    While Not rs.EOF
        Set Data = New LookupData
        If not IsNull(rs(1)) Then
            Data.Key = replace(template, "X", rs(1))
        End If

        If not IsNull(rs(2)) Then
            Data.value = rs(2)
        End If

        joinlookupall.Add Data
        rs.MoveNext
    Wend
End Function

Then call the function as 

speclook(sometext, "*X*")
speclook(sometext, "*" + chr(32) + "X" + chr(32) + "*")
etc

Upvotes: 1

Related Questions