Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Forms Programming / December 2005

Tip: Looking for answers? Try searching our database.

Error 3065- cannot execute select query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Seren - 12 Dec 2005 20:02 GMT
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

Signature

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

Reply to this Message
Klatuu - 12 Dec 2005 21:05 GMT
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...
>
[quoted text clipped - 34 lines]
> Much appreciated!!
> Seren
Reply to this Message
Seren - 13 Dec 2005 13:59 GMT
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?

Signature

"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
[quoted text clipped - 44 lines]
> > Much appreciated!!
> > Seren
Reply to this Message
Klatuu - 13 Dec 2005 14:36 GMT
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
[quoted text clipped - 51 lines]
> > > Much appreciated!!
> > > Seren
Reply to this Message
Seren - 20 Dec 2005 18:28 GMT
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
Signature

"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.
[quoted text clipped - 54 lines]
> > > > Much appreciated!!
> > > > Seren
Reply to this Message
Klatuu - 20 Dec 2005 18:52 GMT
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 & "#"

> ok, here is my new code:
>
[quoted text clipped - 86 lines]
> > > > > Much appreciated!!
> > > > > Seren
Reply to this Message
Seren - 21 Dec 2005 12:38 GMT
Well, I'm not getting any errors anymore, but my subform is still blank...
Signature

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

> The syntax as posted is for a numeric field:
>  "tblPanelJob.Operation= " & cboOperation.Value & " AND
[quoted text clipped - 96 lines]
> > > > > > Much appreciated!!
> > > > > > Seren
Reply to this Message
Seren - 21 Dec 2005 12:46 GMT
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]=##'.
Signature

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

> The syntax as posted is for a numeric field:
>  "tblPanelJob.Operation= " & cboOperation.Value & " AND
[quoted text clipped - 96 lines]
> > > > > > Much appreciated!!
> > > > > > Seren
Reply to this Message
Klatuu - 21 Dec 2005 14:28 GMT
tblPanelJobDetail.OperationDetail = " & _
cboOperationDetail.Value & #" AND tblResults.[date] = " & txtDate.Value & "#"

> 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:
[quoted text clipped - 102 lines]
> > > > > > > Much appreciated!!
> > > > > > > Seren
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.