[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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