Access queries from query designer

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!

Answer 1

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

Answer 2

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.

Answer 3

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?

Answer 4

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...

Answer 5

Hi,

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

Just curious...

Answer 6

Thank you for your answer!

 Ok I solved it like this:

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

and they have their EXCEL!!! Haha..

Answer 7

Hi,

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

Answer 8

yes THX

just rying to get the replies in cronological order

Answer 9

yep is sort of quick and dirty fix...

but it works for now

Answer 10

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 [e-mail].