Results 1 to 6 of 6
  1. #1
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    17

    Need help with query to SQL and inserting into VBA

    Hello all,



    I keep running into the same problem which I'm assuming is probably an easy fix but I'm just not seeing it. The error I get is "Query input must contain at least one table or query". First, I built my query. I tested my query and it seems to work. What I'm doing is pulling info from two tables (Comms2 and Misc) and adding two calculated fields (Expr1 and Expr2) all of which appends to my table "Override". I've converted the query to SQL and put it into my VBA. I have done this to other queries with success, so I'm fairly certain I cleaned up the SQL code correctly. My query does call for a vba code for the calclations but I don't think that is what is causing the current error...however, my other queries didn't call for vba coding. Below is the code.

    I appreciate any help anyone can give me.
    Thanks,
    John

    Public Sub appOverride(commDate)
    Dim appOver As String

    appOver = "INSERT INTO Override ( DateofComm, Instrument, MiscAmount, Store, SP1, factorBasis, overrideTotal )" & _
    "SELECT Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr1, [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) AS Expr2" & _
    "FROM Comms2 INNER JOIN Misc ON Comms2.DateofComm = Misc.DateofComm" & _
    "GROUP BY Misc.DateofComm, Misc.Instrument, Misc.MiscAmount, Misc.Store, Comms2.SP1, getFactor([SP1],[Misc].[Store],[Misc].[Instrument]), [MiscAmount]*getFactor([SP1],[Misc].[Store],[Misc].[Instrument])" & _
    "HAVING Misc.DateofComm= #' & commDate & '# AND Comms2.SP1='Dave M' Or Comms2.SP1='Emily Beckman' Or Comms2.SP1='Anthony T.' AND getFactor([SP1],[Misc].[Store],[Misc].[Instrument]) Is Not Null;"

    DoCmd.RunSQL appOver

    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,626
    put this before the DoCmd line (and comment out that line for now anyway)

    Debug.Print appOver

    then look at the output in the immediate window - you should be able to see that there is more than one error.
    BTW, that is trouble shooting 101.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    17
    I tried what you suggested. I'm sorry, I don't see where the error(s) is.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,626
    AS Expr2FROM << missing space

    Misc.DateofCommGROUP BY << ditto

    Misc.DateofComm= #' & commDate & '# << not a date - supposed to be?

    Surprised you're not seeing those. If you still can't see that I must have a different output than you, for some reason. In that case, perhaps post your output?
    You can often solve such issues by pasting output into a new query sql view and switching to datasheet view. Access usually highlights whatever it is that causes the query to balk, albeit one issue at a time. Regardless of whether or not you use line continuation characters, I suggest you get into the habit of putting your required spaces at the beginning of a line:

    " SELECT Misc.DateofComm,[Store],[Misc].[Instrument])... & _
    " FROM Comms2 INNER JOIN Misc ON ... & _
    " GROUP BY Misc.DateofComm, ..." & _
    " HAVING Misc.DateofComm..." & -

    EDIT - forgot to mention you might want to consider this approach rather than a whole lot of OR's:
    IN ('Dave M','Emily Beckman','Anthony T.')
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    17
    Thank you, that was a problem but not the problem. Originally it wasn't written like that. I've converted the query several times into sql and always got the same problem thinking I had missed a ( or a ". My last attempt was to make it single quotes instead of double not realizing that my previous attempt had missed a ( somewhere. Works fine now.

    Thank you for seeing what I couldn't.

    Btw, thank you for the tip on the Or's. I was not aware of that.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,626
    You're welcome.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble inserting a text into a query
    By WAVP375 in forum Access
    Replies: 7
    Last Post: 03-26-2019, 03:08 AM
  2. inserting query into table
    By kiranair in forum Access
    Replies: 1
    Last Post: 06-08-2016, 08:01 AM
  3. inserting a note or comment in a query
    By GordonT in forum Queries
    Replies: 2
    Last Post: 09-03-2014, 12:33 PM
  4. inserting query result in a form
    By nichmeg in forum Forms
    Replies: 1
    Last Post: 10-17-2011, 06:42 AM
  5. Replies: 2
    Last Post: 05-03-2011, 01:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums