424,279 Members | 1,905 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Syntax Error in String in Query Expression - Run time error 3075

P: 4
I've seen many similar threads, but despite repeated efforts I cannot figure out my problem. I am running Access 2003, VB 6.5, Office XP Pro. Code excerpt is below (you can see where I've tried debugging myself). My problem is in the DLookup command.

UserName = Me.cboUserName.Value
Debug.Print "User Name is "; UserName
strPassword = DLookup("Password", "Employees", "EmpName ='" & UserName)
Debug.Print "Password is "; Password
If Me.txtPassword.Value = strPassword Then
DoCmd.Close acForm, "DatabaseLogin", acSaveNo
DoCmd.OpenForm "MenuMain", acViewNormal
Exit Sub

"cboUserName" is a combo box on form "DatabaseLogin". That is working fine. On same form users enter a password into text box "txtPassword". I am attempting to compare value of "txtPassword" to a cell "Password" in table "Employees" where "EmpName" is equal to value of "cboUserName". For explanation purposes only, let's say value of "cboUserName" is "Davis". With syntax as shown above, I receive error message {Syntax error in string in query expression 'EmpName ='Davis'.} (items inside braces are verbatim). "EmpName" is a text field. "Password" is also a text field.

I have tried multilple variations of syntax, getting other error messages in the process (compile error, you canceled the previous operation ,etc.). What am I doing wrong? Thanks in advance for your help.
Nov 13 '09 #1

✓ answered by ajalwaysus

Try this:
Expand|Select|Wrap|Line Numbers
  1. strPassword = DLookup("Password", "Employees", "EmpName ='" & UserName & "'")
  2. Exit Sub
You need to wrap the UserName in single quotes on both ends of the value.

Hope this helps,
-AJ

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 266
Try this:
Expand|Select|Wrap|Line Numbers
  1. strPassword = DLookup("Password", "Employees", "EmpName ='" & UserName & "'")
  2. Exit Sub
You need to wrap the UserName in single quotes on both ends of the value.

Hope this helps,
-AJ
Nov 13 '09 #2

P: 4
Thank you! It worked perfectly.
Nov 13 '09 #3

NeoPa
Expert Mod 15k+
P: 31,186
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
Nov 15 '09 #4

P: 4
Thank you, NeoPa for your insight. It's helpful to a novice like me.
Nov 16 '09 #5

NeoPa
Expert Mod 15k+
P: 31,186
I'm very pleased to hear that.

Good luck and Welcome to Bytes!
Nov 16 '09 #6

Post your reply

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