Why doesn't this Dlookup work

[Alan]

OK, I have a table called tblPVDMatch with a list of customerID's in it.

I want to perform a lookup on this table to display a message box if the
CustomerID field on the form matches an entry in this table. Even though I
can see the CustomerID in the table and the corresponding number in the
correct field on the form, it always seems to revert to the else statement's
msgbox (see below). Interestingly, it will output Me.CustomerID as part of
the else statement so I know the value it's meant to be looking for is
correct. This is the code I'm using:

If Me.CustomerID = DLookup("Custom erID", "tblPVDMatc h") Then
MsgBox "Match!" & Me.CustomerID
Else
MsgBox "No match!!" & Me.CustomerID
End If

What am I doing wrong?

Thanks

Alan

[Jason Lepack]

You need to specify criteria. The way you have written it the dlookup
will return the very first record in the tblPVDMatch every time. It
won't even look at the other records.

Check out this Microsoft KB Article on DLookup
http://support.microsoft.com/kb/208786

Based on your description it should be written: (Assumes that
CustomerID is a number)

if not isNull(DLookup( "[CustomerID]", "tblPVDMatc h", "[CustomerID] = "
& Me.CustomerID)) then
MsgBox "Match!" & Me.CustomerID
Else
MsgBox "No match!!" & Me.CustomerID
End If

Cheers,
Jason Lepack

Alan wrote:

OK, I have a table called tblPVDMatch with a list of customerID's in it.

I want to perform a lookup on this table to display a message box if the
CustomerID field on the form matches an entry in this table. Even though I
can see the CustomerID in the table and the corresponding number in the
correct field on the form, it always seems to revert to the else statement's
msgbox (see below). Interestingly, it will output Me.CustomerID as part of
the else statement so I know the value it's meant to be looking for is
correct. This is the code I'm using:

If Me.CustomerID = DLookup("Custom erID", "tblPVDMatc h") Then
MsgBox "Match!" & Me.CustomerID
Else
MsgBox "No match!!" & Me.CustomerID
End If

What am I doing wrong?

Thanks

Alan

[Alan]

You need to specify criteria. The way you have written it the dlookup
will return the very first record in the tblPVDMatch every time. It
won't even look at the other records.

Check out this Microsoft KB Article on DLookup
http://support.microsoft.com/kb/208786

Based on your description it should be written: (Assumes that
CustomerID is a number)

if not isNull(DLookup( "[CustomerID]", "tblPVDMatc h", "[CustomerID] = "
& Me.CustomerID)) then
MsgBox "Match!" & Me.CustomerID
Else

Thanks Jason, I see where I was going wrong now.

Thanks again

Alan