By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Syntax problem using DLookup

100+
P: 101
I am getting a runtime error 2471 on the expression LoginID using Dlookup and dont know how to correct it.
My code is:-
Expand|Select|Wrap|Line Numbers
  1. Dim LoginID As Integer
  2. Dim Priv As Variant
  3. LoginID = [basMyEmpID].lngMyEmpID
  4. On Error GoTo Ad_Err
  5. MsgBox (LoginID)
  6.                      Priv = DLookup("[strAccess]", _
  7.                      "[tblEmployees]", _
  8.                      "[lngEmpID] = LoginID")
  9.                       MsgBox (Priv)
  10.  
  11.  
  12.                      Exit Sub
  13.  
  14. Ad_Err:
  15.             MsgBox "Error " & Err.Number & vbCrLf & Err.Description
  16.             Exit Sub 
LoginId is an Integer or Long and is evaluated correctly using Debug. But the Priv variant is returning Empty?

Could someone please advise
Nov 16 '11 #1

✓ answered by TheSmileyCoder

There you go
Expand|Select|Wrap|Line Numbers
  1. Priv = DLookup("[strAccess]", _ 
  2.                      "[tblEmployees]", _ 
  3.                      "[lngEmpID] =" & LoginID) 
What you need to understand is that the information you supply in the Dlookup gets sent to the JET database engine, and JET has no idea what LoginID is. Therefore you need to parse the string together first (The correction I made in your code) forcing VBA to piece together the string, before sending it.

Share this Question
Share on Google+
7 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
There you go
Expand|Select|Wrap|Line Numbers
  1. Priv = DLookup("[strAccess]", _ 
  2.                      "[tblEmployees]", _ 
  3.                      "[lngEmpID] =" & LoginID) 
What you need to understand is that the information you supply in the Dlookup gets sent to the JET database engine, and JET has no idea what LoginID is. Therefore you need to parse the string together first (The correction I made in your code) forcing VBA to piece together the string, before sending it.
Nov 16 '11 #2

100+
P: 101
Thank You SmileyCoder, that works just fine. I had tried almost every combination in the syntax but could not get it to work.

It does now......Thanks
Nov 16 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
@Cyd.
More important than the solution is why it's the solution. Smiley tried to explain this after providing the solution and it's important you understand it otherwise you'll forever be posting questions at the same level (More questions we like, but not rehashes of the same ones over again). If you still don't understand then read again what he posted. Fundamentally it comes down to understanding what is done by the VBA code (which creates a SQL syntax string), and what is done by the SQL engine when it is passed that SQL string.
Nov 17 '11 #4

100+
P: 101
Hi NeoPa

Thank you, I was aware that my problem was a Syntax one and I can see that I had missed out the =" & LoginID. I was not aware of the JetEngine parse requirement but am assuming the quotes an ampersand do this.

Thank you for your advice
Nov 17 '11 #5

Expert 100+
P: 446
Hi
Whenever I use DLookup I specify the destination as a Variant just incase nothing is found!
Then test it is not null before proceeding, of course.
S7
Nov 17 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
That's not quite it Cyd, and I'll explain as even minor confusions in this area (such an important area within database work) can cause you so many problems.

The point is that everything between the quotes (") in VBA is actually a string. The ampersand (&) sticks two strings together, and when used with numbers this causes the number to be converted to a string automatically so that string can be added to the other to create the bigger (longer generally) string.

Consider the difference between :
Expand|Select|Wrap|Line Numbers
  1. "[lngEmpID] = LoginID"
and
Expand|Select|Wrap|Line Numbers
  1. "[lngEmpID] = " & LoginID
The former is a single string which results in [lngEmpID] = LoginID. The latter, if we assume for the moment an example value of the Me.LoginID control of your form to be 32 for instance, results in [lngEmpID] = & 32 or [lngEmpID] = 32.

When we look at these two scenarios at the point where these strings are passed over to SQL (via the DLookup() call) we see they both start with [lngEmpID] = . This is fine as the fieldname [lngEmpID] is recognised by SQL. All good so far.

Now we get to the difference between the two versions of the SQL command string. The first compares it to a value LoginID. SQL has no way of understanding this reference as it is actually a control on a form, but not fully specified. SQL wets its pants and stops working. In the second version though, it sees a simple numeric value - 32 - and is happy to continue processing.

The second version works because we have VBA referencing the value of Me.LoginID before adding the result to the other part of the string.
Nov 17 '11 #7

100+
P: 101
Thats very well explained.Many thanks
Nov 19 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.