DLookup error

[janeyg]

Hi

I wonder if anyone can help, I have been staring at this for so long. I just can't see where the missing operator is. Username, strUserName and strEmpName are all text fields. I keep getting the error message

Fun-time error '3075'
Syntax error (missing operator) in query expression 'Username =

If DLookup("Username", "Master", "Username= " & DLookup("strEmpName", "tblEmployees", "strUserName")) Then
MsgBox "Welcome - Username confirmed."

Can any see the missing operator at all? I am trying to look up the username in the Master table which is added to that table when the user logs on, I then want to check it match the information in the login table and form to make sure login details are not swapped/shared and the correct user access the database.

Hope that makes sense! Any help is appreciated.
thanks

 

[pr2-eugin]

Sorry what exactly are you trying to do? You seem to have a DLookUp inside another DLookUp?

Looks like the DLookUp returns a String, wrap it in Single Quotes.. Have you considered capturing Null results?

 

[namliam]

Handling this inside a single query would not only be easier but also faster

 

[janeyg]

Thank you namliam and pr2-eugin for coming back to me. I was looking for a way to cross reference between two tables. The Master table logs the computer user, this username is also in my employees table which I use with the login form. Someone on this forum suggested using a nested Dlookup to achieve this.

I would use a query but I am unsure how to go about this. I have the query set up but am not sure how to apply the expression, I could not get it to work. I tried to caputure nulls but again I kept getting a syntax error.

I really wanted to check the Master table which records the Username against the record containing strEmpName and trUserName, in "tblEmployees". This way I can confirm the correct user logged in matches the user details in the employees table wth message to say - user confirmed or user not recognised.

any advice would be appreciated.
thank you

 

[namliam]

Well for the double DLookup you are definatly missing the """ wrapping

If DLookup("Username", "Master", "Username= """ & DLookup("strEmpName", "tblEmployees", "strUserName") & """") Then
MsgBox "Welcome - Username confirmed."

Should work.... though you need to work on your understanding of dlookup since the second dlookup will not work there is no equation at the end, you only have "strUserName"

As for the query, simply get the SQL and stick it in code, opening the query in a recordset
dim rst as dao.recordset
set rst = "Select ... from .... where Username = """ & strUserName & """"
then do some ... If rst!SomeColumn = strUsername then Msgbox "OK"

 

[janeyg]

Thanks namliam, your absolutly correct - my DLookup did not return a result.

Maybe the query is the better route. I have the SQL from the Query as you note below. I will try what you suggest to see if I can get it to work.
thanks!

 

[janeyg]

Hi namliam

but could yo help further, I am really struggling to understand how to apply this in a query. I copy the sql from the query

SELECT Master.Username, tblEmployees.lngEmpID, tblEmployees.strUsername, tblEmployees.strEmpName, tblEmployees.strEmpPassword, tblEmployees.strEmpForm, tblEmployees.strAccess
FROM tblEmployees INNER JOIN Master ON tblEmployees.strUsername = Master.Username;

Please could you advise me in more detail how to apply it? I am new to this and its a steep learning curve.
Any help is appreciated.
thanks

 

[namliam]

Well how would you search a particular user? By applying a where cluase:
Where strUsername = "Namliam"

In code it would convert to something like:

dim mySQL as string, VariableUsername as string
VariableUsername = "Namliam"
mySQL = " SELECT Master.Username, tblEmployees.lngEmpID, tblEmployees.strUsername, tblEmployees.strEmpName, tblEmployees.strEmpPassword, tblEmployees.strEmpForm, tblEmployees.strAccess " & _ 
        " FROM tblEmployees " & _ 
        " INNER JOIN Master ON tblEmployees.strUsername = Master.Username " & _ 
        " Where strUsername = """ & VariableUsername & """"

 

[janeyg]

thanks namliam!