DLookup Problem

[deadelvis]

Hi guys

Can anyone tell me why im getting a type mismatch error with the code below? And an alternative that works would be a bonus.

As u can see im trying to extract a value from a table based on 2 values from my form.


Private Sub ProjectId2_AfterUpdate()


Dim strFilter1, strFilter2 As String

strFilter1 = "StaffID = " & Me!StaffID
strFilter2 = "ProjectID = " & Me!ProjectId2


Me!ActRole = DLookup("ProjectRole", "tblProjectStaff", strFilter1 And strFilter2)


End Sub

Thanks in advance

 

[deadelvis]

Fyi

All the fileds referred to are of the same data type

thanks

 

[supercharge]

Me!ActRole = DLookup("ProjectRole", "tblProjectStaff", strFilter1 And strFilter2)

How about:

strFilter1 = "[StaffID] = " & Me!StaffID
strFilter2 = "[ProjectID] = " & Me!ProjectId2
Me!ActRole = DLookup([ProjectRole], "tblProjectStaff", strFilter1 And strFilter2)

 

[Pat Hartman]

Me!ActRole = DLookup("ProjectRole", "tblProjectStaff", strFilter1 & " And " & strFilter2)

FYI, strFilter1 is declared as a variant, NOT as a string as you seem to think.

Change to:
Dim strFilter1 As String, strFilter2 As String

or, preferably

Dim strFilter1 As String
Dim strFilter2 As String