jjjjjjj
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, before the FROM as well
Definitively, the problem is caused by commas!
Why is there a ; at the end of things?
M
M
to tell that the statement is over
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.
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.
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
M
To make it a little easier, create your SQL in Access design mode, view it in SQL mode and paste it in your code.
ASKER
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
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
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
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
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,"'","'' ") & "'")
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,"'","''
ASKER
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
jjjjjjj
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!
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
While your query won't work, put it all on a single line so it could be easier to seek for mistake!
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.
Using Access you will need ; at the end of your select statment. Access SQl Also needs:
String = "text1"
AutoNumber = Text1
ASKER
Thanks, AzraSound. The comma's were the culprit. The parentheses can either stay or remain, either way the SQL now works fine.
jjjjjjj
jjjjjjj
FROM (tbldept INNER JOIN ... ON ....)