I'm using Microsoft Query to bring data into Excel from our Oracle based accounting package. When I type the parameter directly into the query, it returns the record set. But when I prompt for the parameter, it does not return any records. If I prompt on a different field, it works fine. The paramater I need to prompt for (and eventually link to a cell that will contain that param) contains a dash. I believe the dash is what's causing the problem since the other fields work fine. Thanks for any ideas.

Sounds like just the sort of problem you don't want!

I'm not too familiar with this stuff. So, I'm guessing a lot here.

I guess you have already tried obtaining the parameter value directly from the cell? (And obtained the same undesirable results.) If not, well, that is an obvious thing to try. You might be lucky......

Is it possible to open the query in MS Query? And look at the parameter properties - View, Parameters. I wonder if a different data type might help?? Maybe the dash has fooled Query into thinking the field is date type?

What about taking the parameter value from the worksheet cell (on a change event) and programmatically changing the text of the SQL CommandText?

Can you post whatever you do find out so we all know for next time?

regards,
Fazza

We think alike, I did try to link to a cell with the same exact result. I could not find a parameter property anywhere. I'm not exactly sure how to change the text of the sql statement to reference a cell though. I tried doing this through VBA, but before it gets to the parameter, it tells me that what I'm doing is not an option for that type of data base.

You couldn't find the parameter property? I assume then that you can not access the query in MS Query?? I think (guess) via VBA might be more likely to succeed.

I don't understand about "not being an option for that type of database". Maybe you can copy & paste the exact errore message into google and search for it - enclose in double quotes first - "your error message".

Another idea, in the VBE, something I have done is go to the immediate window (CTRL-G) and type in debug.print activeworksheet.querytables(1).commandtext (Modify to suit your actual worksheet.)

Now you can see the SQL text.

Modify it directly - take out and end of line characters, insert spaces, etc, etc - until it is the SQL that you want. Preceed it with a string delimiter " and a following one too. Gives you a string "SELECT ..........."

Then add at the beginning,
activeworksheet.querytables(1).commandtext =
Go to the end of the whole line and hit enter.

This should put the edited string into the query.

To load a cell value into the SQL via VBA, just do the manipulations programmatically.

I didn't check the syntax above - it is from my memory. So you might need to modify a little.

Hope this gives you some ideas.

Fazza