none
Access queries from query designer RRS feed

  • Question

  • Hi,

    I'm trying to automate some queries using VBA.

    The Query Designer is giving me the following SQL statement:

    SELECT [04 DemEvo U L3].[Fixed Brand], [04 DemEvo U L3].Material, [04 DemEvo U L3].ValueDate, [04 DemEvo U L3].SumOfSumOfValue AS [Value], [04 DemEvo U L3].Extraction, [04 DemEvo U L3].Label, [04 DemEvo U L3].Location AS Destination, [04 DemEvo U L3].ProductionSite AS [Production Site], "Net Demand" AS Category
    FROM [04 DemEvo U L3];

    well when I'm embedding this into VBA

    DoCmd.RunSQL "SELECT [04 DemEvo U L3].[Fixed Brand], [04 DemEvo U L3].Material, [04 DemEvo U L3].ValueDate, [04 DemEvo U L3].SumOfSumOfValue AS [Value], [04 DemEvo U L3].Extraction, [04 DemEvo U L3].Label, [04 DemEvo U L3].Location AS Destination, [04 DemEvo U L3].ProductionSite AS [Production Site], "Net Demand" AS Category
    FROM [04 DemEvo U L3];"

    obviously it's not working because of the doubLe quote in "Net Demand" AS Category

    I tried also '"||Net Demand||"' but it's still not working.

    What is the correct syntax?

    wouldn't be an imporvement if Query Brwoser would produce an SQL statement which can be embedded into VBA without prb?

    Thank you!

    Tuesday, October 10, 2017 12:03 PM

Answers

  • Thank you for your answer!

     Ok I solved it like this:

    DoCmd.OutputTo acOutputQuery, "05 DemEvo Final", acSpreadsheetTypeExcel12

    and they have their EXCEL!!! Haha..

    • Proposed as answer by Terry Xu - MSFT Thursday, October 12, 2017 1:49 AM
    • Marked as answer by BMSBAJ Thursday, October 12, 2017 7:46 AM
    Wednesday, October 11, 2017 3:42 PM

All replies

  • Try

    DoCmd.RunSQL "SELECT [04 DemEvo U L3].[Fixed Brand], [04 DemEvo U L3].Material, [04 DemEvo U L3].ValueDate, [04 DemEvo U L3].SumOfSumOfValue AS [Value], [04 DemEvo U L3].Extraction, [04 DemEvo U L3].Label, [04 DemEvo U L3].Location AS Destination, [04 DemEvo U L3].ProductionSite AS [Production Site], ""Net Demand"" AS Category " & vbCrLf & _
    " FROM [04 DemEvo U L3];"
    That said, what will using RunSQL achieve for a SELECT query?  If you want to work with the records, then you'd need to use OpenRecordset.  Perhaps if you explain further, we could further guide you.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, October 10, 2017 12:40 PM
  • well when I'm embedding this into VBA

    "SELECT [04 DemEvo U L3].[Fixed Brand], [04 DemEvo U L3].Material, [04 DemEvo U L3].ValueDate, [04 DemEvo U L3].SumOfSumOfValue AS [Value], [04 DemEvo U L3].Extraction, [04 DemEvo U L3].Label, [04 DemEvo U L3].Location AS Destination, [04 DemEvo U L3].ProductionSite AS [Production Site], "Net Demand" AS Category
    FROM [04 DemEvo U L3];"

    Hi BMSBAJ,

    When you embed the sql-string in VBA you enclose it with doublequotes. This gives a conflict with the doublequotes around "Net Demand".

    Change one of the two uses to singlequotes, for instance:

    "SELECT [04 DemEvo U L3].[Fixed Brand], [04 DemEvo U L3].Material, [04 DemEvo U L3].ValueDate, [04 DemEvo U L3].SumOfSumOfValue AS [Value], [04 DemEvo U L3].Extraction, [04 DemEvo U L3].Label, [04 DemEvo U L3].Location AS Destination, [04 DemEvo U L3].ProductionSite AS [Production Site], 'Net Demand' AS Category FROM [04 DemEvo U L3];"

    Imb.

    • Proposed as answer by Terry Xu - MSFT Wednesday, October 11, 2017 7:56 AM
    Tuesday, October 10, 2017 12:58 PM
  • Hi,

    What do you mean by "not working?" I believe the RunSQL method is only applicable to Action Queries (UPDATE, DELETE, INSERT), but you're trying to use it against a SELECT query.

    As Daniel asked, what were you trying to accomplish with this query?

    Tuesday, October 10, 2017 11:38 PM
  • Hi,

    So, are you saying you want to display the result of the UNION query to the user? If so, try using the OpenQuery method rather than RunSQL. For example:

    DoCmd.OpenQuery "05 DemEvo Final"

    Hope it helps...

    Wednesday, October 11, 2017 2:41 PM
  • Hi,

    Did you delete your last post? Did you see my last reply?

    Just curious...

    Wednesday, October 11, 2017 3:38 PM
  • Thank you for your answer!

     Ok I solved it like this:

    DoCmd.OutputTo acOutputQuery, "05 DemEvo Final", acSpreadsheetTypeExcel12

    and they have their EXCEL!!! Haha..

    • Proposed as answer by Terry Xu - MSFT Thursday, October 12, 2017 1:49 AM
    • Marked as answer by BMSBAJ Thursday, October 12, 2017 7:46 AM
    Wednesday, October 11, 2017 3:42 PM
  • Hi,

    Congratulations! Glad to hear you found a solution. Good luck with your project.

    Wednesday, October 11, 2017 3:46 PM
  • yes THX

    just rying to get the replies in cronological order


    Wednesday, October 11, 2017 3:51 PM
  • yep is sort of quick and dirty fix...

    but it works for now

    Wednesday, October 11, 2017 3:52 PM
  • Hi BMSBAJ,

    I'm glad to hear that your issue has been resolved and thanks for sharing your solution. I would suggest you mark your solution to close this thread. If you have any other issue, please feel free to post threads to let us know. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 1:56 AM