dlookup data type mismatch error

[jessss]

[SOLVED] dlookup data type mismatch error

HI,

I am trying to get my VBA module to find the ID of a Member Number from a table using a dlookup but I keep getting a data type mismatch error,

Dim Answer As String
Dim varX As Variant
Dim rst As Recordset
Dim strSQL As Integer
Dim stWhereStr As String
Set db = CurrentDb()

Answer = InputBox("Enter Member Number", "Member Number")

If Answer = "" Then
    MsgBox "Please enter a value into the text box"
    Exit Sub
End If

varX = DLookup("ID", "Files", "Member Number =" & Answer)
MsgBox ID

In the table the ID field is an automatic number and the Member Number is a short text field. Can anyone help me?

Thank you

 

[CJ_London]

if member number is text then you need to use quotes

DLookup("ID", "Files", "Member Number ='" & Answer & "'")

 

[pbaldy]

You'll also need to bracket the field name, due to the inadvisable space.

 

[jessss]

Thank you for help, I changed the dlookup to DLookup("ID", "Files", "Member Number ='" & Answer & "'") and it now works.

Thank you