Using MSQuery for Excel, I have created a query linked to an outside data
source that uses a parameter that is linked (auto-prompted) to a cell on my
worksheet. That works very well. Now I want to group the rows (within the
query) and sum the values. This doesn't work!

However, it does work for me as long as I supply the parameter within the
query grid. However, if I want it to prompt me for the parameter, it gives me
the error.. "Invalid column name 'mydata'. Statement(s) could not be
prepared."

Why is it doing this? And.. How can I get around it???
--
Thanks! cs

I find it to my advantage to record a macro while writing a MSQuery. Then
just run the macro whenever I want the data. This gives me easy access to
Edit the Query at will, as well as to provide additional processing to the
data once it is retrieved.

Vaya con Dios,
Chuck, CABGx3



"Cydney" wrote:

Using MSQuery for Excel, I have created a query linked to an outside data
source that uses a parameter that is linked (auto-prompted) to a cell on my
worksheet. That works very well. Now I want to group the rows (within the
query) and sum the values. This doesn't work!

However, it does work for me as long as I supply the parameter within the
query grid. However, if I want it to prompt me for the parameter, it gives me
the error.. "Invalid column name 'mydata'. Statement(s) could not be
prepared."

Why is it doing this? And.. How can I get around it???
--
Thanks! cs

I like that solution! I'm testing it out now. Could you send me an SQL
example using a parameter that exists in the workbook --say cell A1--? I'm
going to be turning the workbook over to another user who's not savvy at all
in MSQuery and I really don't want to turn them loose in there. So, if this
macro works, it would be great!

Thanks! cs


"CLR" wrote:

I find it to my advantage to record a macro while writing a MSQuery. Then
just run the macro whenever I want the data. This gives me easy access to
Edit the Query at will, as well as to provide additional processing to the
data once it is retrieved.

Vaya con Dios,
Chuck, CABGx3



"Cydney" wrote:

Using MSQuery for Excel, I have created a query linked to an outside data
source that uses a parameter that is linked (auto-prompted) to a cell on my
worksheet. That works very well. Now I want to group the rows (within the
query) and sum the values. This doesn't work!

However, it does work for me as long as I supply the parameter within the
query grid. However, if I want it to prompt me for the parameter, it gives me
the error.. "Invalid column name 'mydata'. Statement(s) could not be
prepared."

Why is it doing this? And.. How can I get around it???
--
Thanks! cs

Also.. I'm using an ODBC connection. Please include that type in your SQL.

Also.. In my test, I see that it hard-codes in my personal PC ID. Which
means, I guess, that I couldn't run it successfully on someone else's PC. Can
I get around that?
--
Thanks cs


"Cydney" wrote:

I like that solution! I'm testing it out now. Could you send me an SQL
example using a parameter that exists in the workbook --say cell A1--? I'm
going to be turning the workbook over to another user who's not savvy at all
in MSQuery and I really don't want to turn them loose in there. So, if this
macro works, it would be great!

Thanks! cs


"CLR" wrote:

I find it to my advantage to record a macro while writing a MSQuery. Then
just run the macro whenever I want the data. This gives me easy access to
Edit the Query at will, as well as to provide additional processing to the
data once it is retrieved.

Vaya con Dios,
Chuck, CABGx3



"Cydney" wrote:

Using MSQuery for Excel, I have created a query linked to an outside data
source that uses a parameter that is linked (auto-prompted) to a cell on my
worksheet. That works very well. Now I want to group the rows (within the
query) and sum the values. This doesn't work!

However, it does work for me as long as I supply the parameter within the
query grid. However, if I want it to prompt me for the parameter, it gives me
the error.. "Invalid column name 'mydata'. Statement(s) could not be
prepared."

Why is it doing this? And.. How can I get around it???
--
Thanks! cs