RE: Error 3065- cannot execute select query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > Sounds like you are missing references. Open your VB editor, select
> > > Tools-->References, and look for a reference to DAO 3.60 Library.
> > >
> > > "Seren" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > "Behave like a duck- keep calm and unruffled on the surface but paddle like
> > > > > > hell underneath."
.

Relevant Pages

  • Re: **Pop-up message prompting for user information**
    ... Only reason I want this is because our userID's are weird numbers and people ... "Susan" wrote: ... Dim sUser As String ...
    (microsoft.public.excel.programming)
  • Re: Inserting rows syntax + row #ing code in protected sheet?
    ... mentioned WP above because I'm going to reference a handy piece of syntax I ... is there is an extra command to handle "cancel" in VB? ... > Dim i As Long ... > Exit Sub ...
    (microsoft.public.excel.programming)
  • Re: Betovs notation
    ... That might apply to RosAsm... ... >:)) You just give the reason why an Assembler should NEVER ... That's not Nasm syntax, maybe it's LuxAsm syntax... ... > the readability, ... ...
    (alt.lang.asm)
  • Doubleclick on a treeview node - sometimes it works and sometimes not
    ... The productive database had lost one required reference - for what reason ... Dim lst As String ... lngVorgaenger As Long) ... Set rst = db.OpenRecordset("SELECT lngQuotationNr, ...
    (microsoft.public.access.formscoding)
  • Re: Using query values for field name in table
    ... You didn't need to change the syntax on the Fields collection to get it to ... See http://www.QBuilt.com for all your database needs. ... >>> Dim dbsGeneralThoracic As Database ... >>> Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)