Syntax problem using DLookup

[Cyd44]

I am getting a runtime error 2471 on the expression LoginID using Dlookup and dont know how to correct it.
My code is:-

Dim LoginID As Integer
Dim Priv As Variant
LoginID = [basMyEmpID].lngMyEmpID
On Error GoTo Ad_Err
MsgBox (LoginID)
                     Priv = DLookup("[strAccess]", _
                     "[tblEmployees]", _
                     "[lngEmpID] = LoginID")
                      MsgBox (Priv)
 
 
                     Exit Sub
 
Ad_Err:
            MsgBox "Error " & Err.Number & vbCrLf & Err.Description
            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

[TheSmileyCoder]

There you go

Priv = DLookup("[strAccess]", _ 
                     "[tblEmployees]", _ 
                     "[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.

[Cyd44]

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

[NeoPa]

@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.

[Cyd44]

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

[sierra7]

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

[NeoPa]

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 :

"[lngEmpID] = LoginID"

and

"[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.

[Cyd44]

Thats very well explained.Many thanks