problems in running sql query in vba module

[peace77]

Hello,

I am trying to run a query through vba module in ACCESS 2003 but I get runtime error 2342: “A Run SQL action requires an argument consisting of an SQL statement”. I am not sure why I am getting this error.

This is to code I have written:

Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryMS_TNA_Ser1")


strSelect = "[Historical Nav Data].FundNumber, [Historical Nav Data].Class, Sum([Historical Nav Data].MarketValue) AS SumOfMarketValue, Sum([Historical Nav Data].NetCash) AS SumOfNetCash, Sum([MarketValue]+[NetCash]) AS [Total TNA]"
strFrom = "[Historical Nav Data]"
strWhere = " ((([Historical Nav Data].PricingDate) = #3/10/2008#)) "
strGroup = "[Historical Nav Data].FundNumber, [Historical Nav Data].Class"
strHaving = "((([Historical Nav Data].Class)=""A""));"

 strSQL = "SELECT " & strSelect & " FROM " & strFrom & " WHERE " & strWhere & "GROUP BY" & strGroup & "HAVING" & strHaving
    DoCmd.RunSQL strSQL

I would appreciate if someone tell me where I am going wrong.

Thanks for the help

Peace

 

[jdraw]

Hello,

I am trying to run a query through vba module in ACCESS 2003 but I get runtime error 2342: “A Run SQL action requires an argument consisting of an SQL statement”. I am not sure why I am getting this error.

This is to code I have written:

Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryMS_TNA_Ser1")


strSelect = "[Historical Nav Data].FundNumber, [Historical Nav Data].Class, Sum([Historical Nav Data].MarketValue) AS SumOfMarketValue, Sum([Historical Nav Data].NetCash) AS SumOfNetCash, Sum([MarketValue]+[NetCash]) AS [Total TNA]"
strFrom = "[Historical Nav Data]"
strWhere = " ((([Historical Nav Data].PricingDate) = #3/10/2008#)) "
strGroup = "[Historical Nav Data].FundNumber, [Historical Nav Data].Class"
strHaving = "((([Historical Nav Data].Class)=""A""));"

 strSQL = "SELECT " & strSelect & " FROM " & strFrom & " WHERE " & strWhere & "GROUP BY" & strGroup & "HAVING" & strHaving
    DoCmd.RunSQL strSQL

I would appreciate if someone tell me where I am going wrong.

Thanks for the help

Peace

Your constants "GROUP BY" and "HAVING" should have leading and trailing spaces " GROUP BY " and " HAVING ".
What is the purpose of your qdf variable?

What do you get when you comment out the DoCmd....
and replace it with
Debug.print strSql?
Can you take the Debug.print output , copy it and make it the SQL for a query?

 

[boblarson]

Actually, your problem ALSO lies in that you are trying to run a Select statement with DoCmd.RunSQL which only works for Action queries (delete, append, insert, etc.).

What is the purpose of the query? To use a select statement you need to either use the DoCmd.OpenQuery or assign it to a recordset.

 

[pbaldy]

In addition to jdraw's accurate assessment, RunSQL can only be used with an action query (append, update...). If you want to get a value back from that query and do something with it, you'll need to open a recordset or something. Perhaps an overview of your goals will help us provide the proper solution.

 

[peace77]

Hello All,

Thanks so much for your replies. I am novice in Access and probably I am going round about to work out on my solution.
I actually have designed 4 queries within database in design view to generate the desired results. All these queries are either select or
union queries and constants like group by or having etc.

I am now designing a form so that the user just enters the date (which is a criterion for two queries) and clicks on
just one button to generate the desired results instead of running the 4 queries one by one.

I thought I will create a command button on that form and create a code on the click event in the vba module and run those 4 queries
from them. I could not find anyother way to run those 4 queries at a go. And i must also mention that I am not an expert in vba either


To answer Jdraw's question, I made the correction on leading and trailing spaces and ran the output of Debug.Print strSQL on SQL query
and it worked fine. But doesnt work within vba module.


From Boblarson and Pbaldy's suggestion i tried openquery method to run the 4 queries that i have already designed and it seems to be working
well and is meeting my objective. I would appreciate if you could confirm that is the right way of doing it.


Also I am having problem in the form where the user inputs the tade and two of my queries uses this date in the criteria field. (I refered
to the forums and created the form based on provided suggestions there)

I have put the criteria within those 2 queries as : [Forms]![frmMS Holdings]![txtDate]

And once I input the date in the form and click on the command button (to run those queries) I get the inpput message:

Enter the parameter value:
[Forms]![frmMS Holdings]![txtDate]

I have to enter these dates 4 times to run those two queries??


Is there a way to avoid this messasge so that the user can enter the date only once.



Thanks so much for the help

Regards,

Peace77

 

[pbaldy]

Having the user enter the date in a form is the way to go (not sure about OpenQuery though; most of us do not expose queries directly to users). If you're getting a parameter prompt, either the form has been closed or the form or control name is misspelled.

 

[peace77]

Thanks Paul,

i had misppeled the form name and now I have got rid of the message.


I have coded the information like this:

Sub ttt()

 DoCmd.OpenQuery "qry_MS TNA (Comp)", , acReadOnly
 DoCmd.OpenQuery "qry_MS TNA (Series)", , acReadOnly
 
 DoCmd.OpenQuery "qry_MS (1) Holdings", , acReadOnly
 DoCmd.OpenQuery "qry_MS (2) ONAL Holdings", , acReadOnly
 DoCmd.OpenQuery "qry_MS (1) All Holdings", , acReadOnly
   
DoCmd.Close acQuery, "qry_MS TNA (Comp)", acSaveYes
DoCmd.Close acQuery, "qry_MS TNA (Series)", acSaveYes
DoCmd.Close acQuery, "qry_MS (1) Holdings", acSaveYes
DoCmd.Close acQuery, "qry_MS (2) ONAL Holdings", acSaveYes


DoCmd.OutputTo acOutputQuery, "qry_MS (1) All Holdings", _
acFormatXLS, "MS Holdings.xls", True


End Sub

I just need to output results of final query “qry_MS (1) All Holdings” and that is why I am using code to close other queries that the user need not see.


I have a general question on access which may sound very silly, but please excuse me for that.


"qry_MS (1) All Holdings” is a union query that combines results of "qry_MS (1) Holdings", and "qry_MS (2) ONAL Holdings"

So do I need to run "qry_MS (1) Holdings", and "qry_MS (2) ONAL Holdings" individually to make "qry_MS (1) All Holdings” run successfully.

Or is it sufficient to just run "qry_MS (1) All Holdings”

Thanks,

Peace77

 

[pbaldy]

You should be able to just run the final query. It will run the others itself in the background.

 

[peace77]

Thanks a lot Paul

You should be able to just run the final query. It will run the others itself in the background.

 

[jason97m]

Just a correction, you can run a SELECT query using DoCMD.RUNSQL, the only issue is that you need to select into a new table for the results, do it like this:
DoCMD.RUNSQL "SELECT * INTO table2 FROM table1; ...this will not yield a 2342 error.

table2 is an arbitrary name, any will do, table1 should be your source.

 

[nasiriyima]

This reply is for people who will stubble across this forum through Google search. Like I did today because I had the same problem running SQL Select Statment using DoCmd.RunQuery. Although I went through all the instruction that I saw in various forums, but I found most process to be too complicated, and I thought hey! there should be an easier way to achieve this without using DoLookup Statement. Anyway here is another solution just in case;

Study the following code and substitute it with your variable names to achieve what you want;

 sSQL = "SELECT Variable FROM GroupTable WHERE VariableCode = '" & Me.comboBox & "'" 
    Set rs = CurrentDb.OpenRecordset(sSQL)
    dbValue = rs!Variable
    MsgBox dbValue, vbOKOnly, "RS VALUE"