By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,286 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Why doesn't this Dlookup work

P: n/a
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("CustomerID", "tblPVDMatch") Then
MsgBox "Match!" & Me.CustomerID
Else
MsgBox "No match!!" & Me.CustomerID
End If

What am I doing wrong?

Thanks

Alan
Jan 17 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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]", "tblPVDMatch", "[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("CustomerID", "tblPVDMatch") Then
MsgBox "Match!" & Me.CustomerID
Else
MsgBox "No match!!" & Me.CustomerID
End If

What am I doing wrong?

Thanks

Alan
Jan 17 '07 #2

P: n/a
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]", "tblPVDMatch", "[CustomerID] = "
& Me.CustomerID)) then
MsgBox "Match!" & Me.CustomerID
Else
Thanks Jason, I see where I was going wrong now.

Thanks again

Alan
Jan 18 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.