471,355 Members | 1,666 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,355 software developers and data experts.

Why doesn't this Dlookup work

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
2 1941
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
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.

Similar topics

4 posts views Thread by David B | last post: by
7 posts views Thread by Tony Williams | last post: by
21 posts views Thread by Thelma Lubkin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.