NEED HELP WITH SQL SYNTAX ON SMALL QUERY 2

[TNN]

What is wrong with this:
cmd.CommandText = "SELECT DISTINCT EMPNO,ACTIVESTAT " _
& "FROM BASSEARN " _
& "WHERE ACTIVESTAT="A"" _
& " ORDER BY EMPNO"
I keep getting syntax error. Have tried everything I can think of.
I can make this work ok if I type the SQL in a text box and then refer to the text property like this:
cmd.CommandText = txtBox.text
Somehow the concantenation(phew!!) must be wrong.

Thank You for any help, anybody.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[culshaja]

You need to replace the " around the A with '. What you are doing is creating a couple of string rather than one.

Use:

cmd.CommandText = "SELECT DISTINCT EMPNO,ACTIVESTAT " _
                & "FROM BASSEARN " _
                & "WHERE ACTIVESTAT='A'" _
                & " ORDER BY EMPNO"

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[ColinM]

try debug.print cmd.commandtext to check your statement
It might be your quotes at the activestat = "A", try single quotes.

 

[TNN]

Thank You James
Thank you ColinM
The double Quotes around the A was the problem.
TNN, Tom
TNPAYROLL@AOL.COM



TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

ColinM,
What does the "Debug.Print cmd.commandtext" do for me? I tried that in the immediate window and got back error message "Object Required". Can I somwhow check my statement there?
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[ColinM]

I sometimes insert debug.print in my code (not the immediate window, this is where the results are displayed) to check strings. When you hover the cursor over a string in debug mode it shows you value, but often if its a long string you can only see the start.
Its almost the same as using:
msgbox str
except the results are in the immediate window and there is no pause.

 

[TNN]

Thank You ColinM, for the explanation.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM