docmd.runsql (1 Viewer)

[jesso]

Hi guys,

I have a problem with OnClick event procedure. I have a button and I want to execute query after clicking that button. Of course. And event I wanna execute is:
sSQL = "PARAMETERS [Forms]![frmOne]![cmbProvincia] Text ( 255 );" & _
"TRANSFORM Sum(product!price*sales!qty) AS Expr1" & _
"SELECT product.product_name " & _
"FROM customer INNER JOIN (product INNER JOIN " & _
"sales ON product.product_id = sales.product_id) ON customer.customer_id = sales.customer_id" & _
"WHERE (((customer.provincia) = [Forms]![frmOne]![cmbProvincia]))" & _
"PIVOT customer.city;"

DoCmd.RunSQL sSQL

and it doesnt work. Where could be a problem? Does runSql support just simple statements? because that statement was generated by access itself, when I created the crosstab query in query designer and switched into sql view and copy pasted it into sSQL variable.

Thanks lot

Pete

 

[jesso]

I found some hint in here to replace docmd.runsql with docmd.openquery but it still does not work. It makes a errormessage Microsoft Office Access can't find the object ....

 

[Deleted member 30250]

I'm not by any means an expert, but your problem is so familiar from when I first started trying to use docmd.runsql. In the end I gave up trying to set up a string like your sSQL, I never seemed to be able to get that right. Instead I put the whole query string directly in the statement:

docmd.runsql "PARAMETERS [Forms]![frmOne]![cmbProvincia] . . . etc.

I drove myself nuts on many occasions until I twigged that you have to be very careful to maintain spaces at the end of your lines before " & _. I think maybe you've lost some? Like here:
"TRANSFORM Sum(product!price*sales!qty) AS Expr1" & _
"SELECT product.product_name " & _
- there isn't a space between Expr1 and SELECT once VBA has 'crossed out' the line break bit.

The way I do it now is to create my query in Access design mode (or as much of it as I can), change to SQL view, then copy and paste into my docmd.runsql statement. Then put the double quotes in, and break up the line with " & _ being very careful not to remove spaces. VBA will whinge until you have all the line break bits and the closing quotes in order, but I've got used to it.

 

[jesso]

I'm not by any means an expert, but your problem is so familiar from when I first started trying to use docmd.runsql. In the end I gave up trying to set up a string like your sSQL, I never seemed to be able to get that right. Instead I put the whole query string directly in the statement:

docmd.runsql "PARAMETERS [Forms]![frmOne]![cmbProvincia] . . . etc.

I drove myself nuts on many occasions until I twigged that you have to be very careful to maintain spaces at the end of your lines before " & _. I think maybe you've lost some? Like here:
"TRANSFORM Sum(product!price*sales!qty) AS Expr1" & _
"SELECT product.product_name " & _
- there isn't a space between Expr1 and SELECT once VBA has 'crossed out' the line break bit.

The way I do it now is to create my query in Access design mode (or as much of it as I can), change to SQL view, then copy and paste into my docmd.runsql statement. Then put the double quotes in, and break up the line with " & _ being very careful not to remove spaces. VBA will whinge until you have all the line break bits and the closing quotes in order, but I've got used to it.

Hey Rob,

thnx for ur answer. I know that spaces are important and dont know why there were not some spaces in this message, but in my code there were spaces. To check the whole statement I put there Me.lblStatus.Caption = sSQL and the statement seems to be right. I tried your advice to put the string, not the variable and the error message that occurs is different...now is telling me that "syntax error (missing operator) in query expression '(((customer.provincia) = [Forms]![frmOne]![cmbProvincia])) PIVOT customer.city;' So Im trying to fix it.

Thank you one more time

 

[Deleted member 30250]

I hate that error message.

In the past I've found I had missing or misplaced brackets and capitals/lower case errors in field names. I've often put [] in even when it doesn't seem necessary. Are you missing a ) in your WHERE clause?

 

[jesso]

OK, I found out that according to SQL view in query designer is missing a GROUP BY function. after I put it there, it tells me "Run time error '2342' A RunSQL action requires an argument consisting of an SQL statement". Holly pig.

 

[jesso]

OK, I found out that there is missing GROUP BY function and after I put it there it writes me thah runtime error 2342, a runSQL action requires an argument consisting of an SQL statement. so it is bad at all I think

 

[CyberLynx]

Looking at your initial query...you indicated that Access created the SQL string. Did it also place the semicolon at the end of:

sSQL = "PARAMETERS [Forms]![frmOne]![cmbProvincia] Text ( 255 );" & _

As I understand it, the semicolon is the very last item on a SQL string (which Access doesn't need anyways).

.

 

[jesso]

Looking at your initial query...you indicated that Access created the SQL string. Did it also place the semicolon at the end of:

sSQL = "PARAMETERS [Forms]![frmOne]![cmbProvincia] Text ( 255 );" & _

As I understand it, the semicolon is the very last item on a SQL string (which Access doesn't need anyways).

.

Hello,

yes the semicolons are rigt there. After time I found out that I forger the GROUP BY function. when I put it there, next err.message occured run-time error '2342' an runsql action requires an argument consistin of a sql statement . and when I removed the semicolons, it wrote me something like there is an error in parameters statement...an here you define parameters, so it is the very end of it and then begins the statement itself. but without parameter clause doesnt work statement in query designer either.

 

[Deleted member 30250]

Your SQL statement is quite complex. Have you tried building it up in stages from a simple SELECT . . FROM . . INNER JOIN . . WHERE to start with, then adding the clever bits once you've got each stage working? It's the boring old way of trying to narrow down the problem, but that's why only picky people become programmers!