Dlookup returns error 2465 can't find the field | referred to your in expression

We created a dlookup  as shown below which works fine in most cases but if the me.txtAccountLookup contains a hyphen we get the error 2465 cannot find the field '|' referred to in your expression. I cannot see why this expression does not work. Please offer guidance to resolve this issue.
CurAcID = DLookup("[AccountID]", "[Accounts]", "[Account] =" & [Me.txtAccountLookUp] & "")

Open in new window

kikimayhayAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Well then it needs quotes around it for it to work:
CurAcID = DLookup("[AccountID]", "Accounts", "[Account] ='" & Me.txtAccountLookUp.Text & "'")
 If that is still not working then something else is going on besides the Dlookup() statement.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You got an extra quote on the end.  Format it like this for text:
CurAcID = DLookup("[AccountID]", "[Accounts]", "[Account] =" & chr$(34) &  [Me.txtAccountLookUp]  & chr$(34))  
Chr$(34) is the quote character.
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually Chr(34) is a double quote character ... just for clarity ...  Chr(39) is a single quote .

mx
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
kikimayhayAuthor Commented:
I applied the code you provided however I receive the same results
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
try this - no brackets

CurAcID = DLookup("[AccountID]", "[Accounts]", "[Account] =" & Chr(34) & Me.txtAccountLookUp & Chr(34) )

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
try it this way without the square brackets:
CurAcID = DLookup("[AccountID]", "[Accounts]", "[Account] =" & chr$(34) &  Me.txtAccountLookUp  & chr$(34))  
 and if that still fails, but a break point on the line and execute the code.  When it stops, go to the debug window and type:
 Debug.? Me.txtAccountLookup
 and you'll probably figure out what's wrong.  I'm assuming this is executing in a form and the account lookup is based on a text field and not a numeric.
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
also ... maybe "[Account] =" should be "[Accounts] ="
                                                                               ^

mx
                                                                       
0
 
kikimayhayAuthor Commented:
using your code produced ole errors my original dllookup will work as long as the textbox does not have any alpha chacters in it, and just found out it drops leading zeros.
 

CurAcID = DLookup("[AccountID]", "Accounts", "[Account] =" & Me.txtAccountLookUp.Text)

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<CurAcID = DLookup("[AccountID]", "Accounts", "[Account] =" & Me.txtAccountLookUp.Text) >>
This is treating it as if it was a numeric field. Is Account ID numeric or text?
JimD.
0
 
kikimayhayAuthor Commented:
it's a simple text box so - text
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I meant Account, not Account ID
JimD
0
 
kikimayhayAuthor Commented:
sorry Account ID is an autonumber field
Account is a text field
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If it's Text ... and [Account]  - the field in the table has a Data Type of Text ...   then what we both posted

CurAcID = DLookup("[AccountID]", "[Accounts]", "[Account] =" & Chr(34) & Me.txtAccountLookUp & Chr(34) )

should work

mx
0
 
kikimayhayAuthor Commented:
That worked
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.

All Courses

From novice to tech pro — start learning today.