• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 867
  • Last Modified:

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

0
kikimayhay
Asked:
kikimayhay
  • 5
  • 5
  • 4
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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 MVP, Access and Data Platform)Commented:
Actually Chr(34) is a double quote character ... just for clarity ...  Chr(39) is a single quote .

mx
0
 
kikimayhayAuthor Commented:
I applied the code you provided however I receive the same results
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this - no brackets

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

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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 MVP, Access and Data Platform)Commented:
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)PresidentCommented:
<<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)PresidentCommented:
I meant Account, not Account ID
JimD
0
 
kikimayhayAuthor Commented:
sorry Account ID is an autonumber field
Account is a text field
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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