Error 3065- cannot execute select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
 
The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)
 
in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

Seren said:
This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
 
Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

Seren said:
in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

Seren said:
This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
ok, here is my new code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSelectSQL As String


strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail, tblResults.[date]," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectSQL)


and I'm getting the following error:

Run-time error '3075':
syntax error (missing operator) in query expression 'tblPanelJob.Operation =
AND tblPanelJobDetail.OperationDetail= AND tblResults.[date]='.

So, it's not taking the info concatinated with it for some reason. Any ideas?

Thanks a bunch!
seren
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

Seren said:
in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

:

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
The syntax as posted is for a numeric field:
"tblPanelJob.Operation= " & cboOperation.Value & " AND
If it is a text field, then it should be:
"tblPanelJob.Operation= '" & cboOperation.Value & "' AND

If the date field you are referencing is a date/time type, then you need to
enclose the vaule in #:
" AND tblResults.[date] = " & txtDate.Value
" AND tblResults.[date] = #" & txtDate.Value & "#"

Seren said:
ok, here is my new code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSelectSQL As String


strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail, tblResults.[date]," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectSQL)


and I'm getting the following error:

Run-time error '3075':
syntax error (missing operator) in query expression 'tblPanelJob.Operation =
AND tblPanelJobDetail.OperationDetail= AND tblResults.[date]='.

So, it's not taking the info concatinated with it for some reason. Any ideas?

Thanks a bunch!
seren
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

Seren said:
in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


:

The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

:

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
Well, I'm not getting any errors anymore, but my subform is still blank...
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The syntax as posted is for a numeric field:
"tblPanelJob.Operation= " & cboOperation.Value & " AND
If it is a text field, then it should be:
"tblPanelJob.Operation= '" & cboOperation.Value & "' AND

If the date field you are referencing is a date/time type, then you need to
enclose the vaule in #:
" AND tblResults.[date] = " & txtDate.Value
" AND tblResults.[date] = #" & txtDate.Value & "#"

Seren said:
ok, here is my new code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSelectSQL As String


strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail, tblResults.[date]," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectSQL)


and I'm getting the following error:

Run-time error '3075':
syntax error (missing operator) in query expression 'tblPanelJob.Operation =
AND tblPanelJobDetail.OperationDetail= AND tblResults.[date]='.

So, it's not taking the info concatinated with it for some reason. Any ideas?

Thanks a bunch!
seren
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

:

in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


:

The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

:

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
No, the reason it wasn't getting an error is because I commented out the line
that was giving me the error before... here's the new one:

Syntax error in date in query expression 'tblPanelJob.Operation =" AND
tblPanelJobDetail.OperationDetail =" AND tblResults.[date]=##'.
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The syntax as posted is for a numeric field:
"tblPanelJob.Operation= " & cboOperation.Value & " AND
If it is a text field, then it should be:
"tblPanelJob.Operation= '" & cboOperation.Value & "' AND

If the date field you are referencing is a date/time type, then you need to
enclose the vaule in #:
" AND tblResults.[date] = " & txtDate.Value
" AND tblResults.[date] = #" & txtDate.Value & "#"

Seren said:
ok, here is my new code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSelectSQL As String


strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail, tblResults.[date]," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectSQL)


and I'm getting the following error:

Run-time error '3075':
syntax error (missing operator) in query expression 'tblPanelJob.Operation =
AND tblPanelJobDetail.OperationDetail= AND tblResults.[date]='.

So, it's not taking the info concatinated with it for some reason. Any ideas?

Thanks a bunch!
seren
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

:

in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


:

The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

:

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & #" AND tblResults.[date] = " & txtDate.Value & "#"


Seren said:
No, the reason it wasn't getting an error is because I commented out the line
that was giving me the error before... here's the new one:

Syntax error in date in query expression 'tblPanelJob.Operation =" AND
tblPanelJobDetail.OperationDetail =" AND tblResults.[date]=##'.
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


Klatuu said:
The syntax as posted is for a numeric field:
"tblPanelJob.Operation= " & cboOperation.Value & " AND
If it is a text field, then it should be:
"tblPanelJob.Operation= '" & cboOperation.Value & "' AND

If the date field you are referencing is a date/time type, then you need to
enclose the vaule in #:
" AND tblResults.[date] = " & txtDate.Value
" AND tblResults.[date] = #" & txtDate.Value & "#"

Seren said:
ok, here is my new code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSelectSQL As String


strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail, tblResults.[date]," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value


Set db = CurrentDb
Set rs = db.OpenRecordset(strSelectSQL)


and I'm getting the following error:

Run-time error '3075':
syntax error (missing operator) in query expression 'tblPanelJob.Operation =
AND tblPanelJobDetail.OperationDetail= AND tblResults.[date]='.

So, it's not taking the info concatinated with it for some reason. Any ideas?

Thanks a bunch!
seren
--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


:

Sounds like you are missing references. Open your VB editor, select
Tools-->References, and look for a reference to DAO 3.60 Library.

:

in the line Dim db as Database, Database doesn't appear in the list of
options that pops up. So, I tried using it anyway. But this is the error
that I get: "Compile error: User-defined type not defined."

I think we've tried something like this earlier on, but were running into
the same problem. Why is that not an option for me?


--
"Behave like a duck- keep calm and unruffled on the surface but paddle like
hell underneath."


:

The Execute method only works with Action queries. A select query is not an
action query. Action queries are Update, Append, and Make Table. If you are
trying to use it as a recordset, then the syntax is:

Dim dbf as Database
Dim rst as Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSelectSQL)

:

This may get complicated. We've been playing around with it forever...

I have a form with a subform. the user selects a couple of criteria and
then the subform is supposed to populate depending on that criteria. There
is a results table, whose fields include resultID, OpID, OpNum, date, day,
Shift, and panels. There is a PanelJob table whose fields include OpId and
Operation. (Ex: 1, Drill)
There is a PanelJobDetail table whose fields include OpNum, OpID, and
OperationDetail (Ex: 1, 1, Drilled Panel). As if that isn't confusing
enough....


The subform needs to show Operation, OperationDetail, date, day, Shift, and
Shift_Units (# of units completed during the user's shift). this is our sql
statement, which includes an inner join to get Operation and Operation detail
*instead of*OpId and OpNum...

Dim strSelectSQL As String
strSelectSQL = "SELECT tblPanelJob.Operation,
tblPanelJobDetail.OperationDetail,tblResults.date," & _
"tblResults.day, tblResults.Shift, tblResults.panels FROM " & _
"(tblPanelJob INNER JOIN tblPanelJobDetail ON
tblPanelJob.OpID=tblPanelJobDetail.OpID)" & _
"INNER JOIN tblResults ON (tblPanelJob.OpID=tblResults.OpID) AND " & _
"(tblPanelJobDetail.OpNum=tblResults.OpNum) WHERE " & _
"tblPanelJob.Operation= " & cboOperation.Value & " AND
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & " AND tblResults.[date] = " & txtDate.Value

CurrentDb.Execute (strSelectSQL)

We've been over and over the SQL statement, and we're just not seeing it.
Now, the error we're getting is Run-Time Error 3065: "Cannot Execute a Select
Query"

Can someone help me figure this one out?

Much appreciated!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
Back
Top