SQL won't work

I have a data control on a form which is used to link to an Access Db.

In the formload event of the form I have the following code:

Set dbs = OpenDatabase("c:\my documents\employees.mdb")

In the general declarations section:

Option Explicit
Dim dbs As Database
Dim rst As Recordset

And in a command button:

Private Sub Command1_Click()




Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, " _
& "tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle, " _
& "FROM tbldept INNER JOIN tblemployees ON " _
& "tbldept.DeptNumber = tblemployees.EmpDeptNumber, " _
& "WHERE DeptNumber ='" & Text1.Text & "' ;")

I cannot get the SQL coded properly.  The error is 3141.

What is wrong with the SQL?

TIA,

jjjjjjj
LVL 1
jjjjjjjAsked:
Who is Participating?
 
AzraSoundCommented:
also take out the comma before the WHERE
0
 
AzraSoundCommented:
try putting parenthesis around the FROM clause:

FROM (tbldept INNER JOIN ... ON ....)
0
 
AzraSoundCommented:
sorry, before the FROM as well
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
Definitively, the problem is caused by commas!
0
 
mark2150Commented:
Why is there a ; at the end of things?


M
0
 
AzraSoundCommented:
to tell that the statement is over
0
 
gafftechCommented:
I would create the Query in Access and click on view\sql copy that and paste it in your code.

Also I would:
dim sSql as string

sSql = "Select * from Table1"
sSql = Sql & "Where Table1.ID = 1 "

Debug.Print Sql 'If you copy this 'string into the sql window in access 'and it runs you are good to go.

'Set rst = dbs.OpenRecordset sSqL

Check your Syntax before you even try and open the rs.

0
 
mark2150Commented:
The statement is over when the string ends. Unless this is embedded in a stream of statements you don't need the ";" at the end. This may be causing the SQL engine to think that more SQL is forthcomming when it's not.

M
0
 
Sage020999Commented:
To make it a little easier, create your SQL in Access design mode, view it in SQL mode and paste it in your code.
0
 
jjjjjjjAuthor Commented:
All,

I have done the Access look and paste but it has not helped.  I believe that there is a syntax error in the code that I have written, but I cannot find it.

I will take the suggestions that AzraSound has made and see what happens.

Thanks for the great dialog from all!!

jjjjjjj
0
 
Billy06Commented:
yeah, I agree with Mark.
You won't need ';'.
NO Commas before FROM and before WHERE.

last statement should be:

"where DeptNumber = '" & Text1.Text& "'"

ALSO, are you sure it's _

to continue?
Is it    " & _
 "where


Good Luck
Bill
0
 
Éric MoreauSenior .Net ConsultantCommented:
Why isn't working?

Is your query is this one:

Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, " _
& "tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle " _
& "FROM tbldept INNER JOIN tblemployees ON " _
& "tbldept.DeptNumber = tblemployees.EmpDeptNumber " _
& "WHERE DeptNumber ='" & replace(Text1.Text,"'","''") & "'")
0
 
jjjjjjjAuthor Commented:
Yes, it is, but in VB All of the data after the replace is not needed.  I have used the code that I posted in the past but never with the join spec.  I believe that this is where the problem lies, but can't figure it out.

jjjjjjj
0
 
Éric MoreauSenior .Net ConsultantCommented:
Is DeptNum numeric?

If so your query should be:
Set rst = dbs.OpenRecordset("SELECT tbldept.DeptNumber, tbldept.DeptName, tblemployees.EmpNumber, tblemployees.EmpName, tblemployees.EmpTitle FROM tbldept INNER JOIN tblemployees ON tbldept.DeptNumber = tblemployees.EmpDeptNumber WHERE DeptNumber =" & Text1.Text)

While your query won't work, put it all on a single line so it could be easier to seek for mistake!
0
 
c4marCommented:
go database manager, put all of your SQL statment and test it over there. You should get the result immedailly, and other thing is try not to break the SQL statement into different line. Cuase you may make some mistake when u try to break it into different line. Other method that u can try is go to access and create a queue for your SQL statement, then compare the SQL code inside the access and your old SQL statement. Ususally u are able to get some idea from the SQl code.
0
 
gafftechCommented:


Using Access you will need ; at the end of your select statment.  Access SQl Also needs:

String = "text1"
AutoNumber = Text1
0
 
jjjjjjjAuthor Commented:
Thanks, AzraSound.  The comma's were the culprit.  The parentheses can either stay or remain, either way the SQL now works fine.

jjjjjjj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.