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

Run-time error 3075 syntax error in string in query expression '[lngEmpID]='Kitso

P: 55
I have the following code and have an error on the bold section. What this tries to do:
The Row Source of my combo box is an sql statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Login.strEmpName], [Login.lngEmpID] FROM Login; 
lngEmp is an AutoNumber and strEmpName and strEmpPassword are text. Now if I click my button I want to be able to match the password and username to values in the database. The problem arises on trying to get the username instead of the autonumber.

Here is the code:
Expand|Select|Wrap|Line Numbers
  1. If Me.txtPassword.Value = DLookup("[strEmpPassword]", "Login", "[lngEmpID] = '" & Me.cboEmployee.Value) & "'"  Then
  2.          lngMyEmpID = Me.cboEmployee.Value
  3.  
  4.         'Close logon form and open the login1 screen
  5.         DoCmd.Close acForm, "Login", acSaveNo
  6.         DoCmd.OpenForm "Login1"
  7.  
  8.     Else
  9.       MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
  10.             "Invalid Entry!"
  11.         Me.txtPassword.SetFocus
  12.     End If
Regards
Jun 18 '07 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,365
You need to close off the single quote inside the DLookup function, not outside.
Expand|Select|Wrap|Line Numbers
  1. If Me.txtPassword.Value = DLookup("[strEmpPassword]", "Login", "[lngEmpID] = '" & Me.cboEmployee.Value & "'") Then
Jun 18 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Since EmpID has a lng (long) prefix I'm assuming that it's a numerical datatype, in which case the DLookup statement should not have the extra quotes at the end. Wouldn't

Expand|Select|Wrap|Line Numbers
  1. DLookup("[strEmpPassword]", "Login", "[lngEmpID] = " &  Me.cboEmployee.Value)
be correct?
Jun 18 '07 #3

Rabbit
Expert Mod 10K+
P: 12,365
Since EmpID has a lng (long) prefix I'm assuming that it's a numerical datatype, in which case the DLookup statement should not have the extra quotes at the end. Wouldn't

Expand|Select|Wrap|Line Numbers
  1. DLookup("[strEmpPassword]", "Login", "[lngEmpID] = " &  Me.cboEmployee.Value)
be correct?
Good catch, that may well be it.
Jun 18 '07 #4

P: 55
Good catch, that may well be it.
I have tried that a sevaral times and I get the error:

Run-time error 2001: You cancelled the previous operation.

Let me try to explain further. Because the combo box query selects lngEmpID and strEmpName, this returns the Employee ID number and the Employee name, however only the Employee name is supposed to be displayed.

Regards
Jun 19 '07 #5

Rabbit
Expert Mod 10K+
P: 12,365
I have tried that a sevaral times and I get the error:

Run-time error 2001: You cancelled the previous operation.

Let me try to explain further. Because the combo box query selects lngEmpID and strEmpName, this returns the Employee ID number and the Employee name, however only the Employee name is supposed to be displayed.

Regards
I gathered as much from your previous posts. Make sure the combo box is returning the correct column.

There's nothing wrong with DLookup syntax so the error must be coming from somewhere else. Try compiling and fixing any errors that show up. If you're still having problems, narrow it down to the sub/function that's causing the problem and post the code.
Jun 19 '07 #6

P: 55
I gathered as much from your previous posts. Make sure the combo box is returning the correct column.

There's nothing wrong with DLookup syntax so the error must be coming from somewhere else. Try compiling and fixing any errors that show up. If you're still having problems, narrow it down to the sub/function that's causing the problem and post the code.
The problem was with both the combo box and the DLookup syntax in that:

The combo box is a string (text) and on the DLookup syntax I wanted the lngEmpID (which is a long integer) to return cboEmployee value which is a string. So all in all I wanted to be able to convert the integer into a string, that is, instead of the combo box displaying lngEmpID I want it to display cboEmployee value.

I have since replaced the lngEmpID in the DLookup syntax with strEmpName and its ok.

Now I want to compare both the password value and the access level value to the combo box value. Here is my updated DLookup syntax, is it correct?
Expand|Select|Wrap|Line Numbers
  1. If Me.txtPassword.Value And Me.myAccess.Value = DLookup("[strEmpPassword] & ' ' & [Access_Level]", "Login", "[strEmpName] = '" & Me.cboEmployee.Value & "'") Then
Thank you so much..

Regards,
Jun 20 '07 #7

Rabbit
Expert Mod 10K+
P: 12,365
The combo box is a string (text) and on the DLookup syntax I wanted the lngEmpID (which is a long integer) to return cboEmployee value which is a string. So all in all I wanted to be able to convert the integer into a string, that is, instead of the combo box displaying lngEmpID I want it to display cboEmployee value.
I'm a little confused by this. DLookup can return any type of value indepent of the data types in the where parameter. If you mean that the data returned by the combobox is in a type different from the value of the field in the where condition, then yes that could be a problem. This usually occurs with strings and numbers.
Expand|Select|Wrap|Line Numbers
  1. "Field1 = " & strNumber <-- This will be considered a number even if it is formatted as text as long as the string is made up of numbers.
  2.  
  3. "Field1 = '" & strNumber & "'" <-- This will be considered a string even if it strNumber is composed of only numbers.
  4.  
  5. "Field1 = #" & strNumber & "#" <-- This will be considered a date as long as strNumber is in a recognized date format.
Now I want to compare both the password value and the access level value to the combo box value. Here is my updated DLookup syntax, is it correct?
Expand|Select|Wrap|Line Numbers
  1. If Me.txtPassword.Value And Me.myAccess.Value = DLookup("[strEmpPassword] & ' ' & [Access_Level]", "Login", "[strEmpName] = '" & Me.cboEmployee.Value & "'") Then
Thank you so much..

Regards,
I think you're looking for
Expand|Select|Wrap|Line Numbers
  1. Me.txtPassword & " " & Me.myAccess
Instead of 'And'
And is a logical operator that evaluates true or false. & is a concatenation operator.
Jun 20 '07 #8

P: 55
I ended up making it work this way:

Expand|Select|Wrap|Line Numbers
  1. If Me.txtPassword.Value = DLookup("[strEmpPassword]", "Login", "[strEmpName] = '" _
  2.     & Me.cboEmployee.Value & "'") And Me.myAccess.Value = DLookup("[Access_Level]", _
  3.     "Login", "[strEmpName] = '" & Me.cboEmployee.Value & "'") Then
Thank you for helping me up to this point.

Regards
Jun 21 '07 #9

Rabbit
Expert Mod 10K+
P: 12,365
Not a problem, good luck.
Jun 21 '07 #10

Post your reply

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