Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Syntax error in DLookup

What is wrong with this code?  

If DLookup("[ProdRef]", "tblProducts", "ProdRef = '" & Me.txtProductID.Column(1) & "'") <> Me.txtTransferredProdRef Then


ProdRef is a text field.

I'm getting a syntax error missing operator.

??

--Steve
0
SteveL13
Asked:
SteveL13
2 Solutions
 
mbizupCommented:
The DLookup syntax looks OK, but try using NZ to handle nulls:

If NZ(DLookup("ProdRef", "tblProducts", "ProdRef = '" & Me.txtProductID.Column(1) & "'")) <> Me.txtTransferredProdRef Then

Open in new window

0
 
PatHartmanCommented:
Nothing obvious jumps out at me.  I would start by separating the where argument so you build it before using it in the DLookup().  That way you can stop the code and look at what you are actually sending the function as an argument.
0
 
pdebaetsCommented:
Are there any single quotes in your ProdRef field data?

You may want to try

If DLookup("[ProdRef]", "tblProducts", "ProdRef = " & chr(34) & Me.txtProductID.Column(1) & chr(34)) <> Me.txtTransferredProdRef Then

... which surrounds the value in double quotes.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
mbizupCommented:
Actually, the statement as you have written it doesn't make sense.

You are looking up ProdRef where ProdRef = txtProductID.Column(1)... which is overkill for simply writing txtProductID.Column(1) without the DLookup.

Are you sure ProdRef is the correct field name in the DLookup?  I would think you might need this (looking up TransferredProdRef  instead of ProdRef, but check the field name):

If NZ(DLookup("TransferredProdRef ", "tblProducts", "ProdRef = '" & Me.txtProductID.Column(1) & "'")) <> Me.txtTransferredProdRef Then

Open in new window

0
 
mbizupCommented:
... and of course if you REALLY ARE trying to lookup ProdRef where ProdRef = txtProdID.Column(1), then you can get rid of the DLookup and simplify your comparison to this (Just compare it directly):

If  Me.txtProductID.Column(1) <> Me.txtTransferredProdRef Then

Open in new window



(As an aside, is txtProductID really a combo box?   If not, the Column(1) is going to cause problems)
0
 
SteveL13Author Commented:
Yes there were single quotes but also yes,

If  Me.txtProductID.Column(1) <> Me.txtTransferredProdRef Then

sure made it a lot easier.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now