Long SQL Query Problems

[tobyjuggler]

I have a rather long SQL query to run with several date/time variables in it. The way I've done it in the past is to record a macro, paste the SQL into the query then afterwards turn the dates/times into variables by manually editing the code in the macro, so that next time it is run the SQL picks up the correct date/time and the correct data is retrieved. This has always worked okay in the past. However, the latest query I have is very long and complicated and I now appear to be unable to use the above method due, it appears, to the length of the query. It seems that the macro itself will only accept so many lines of an SQL query.

Please, does anybody know how to get round this problem?

Is it possible to save the query as an external query (.dqy) and also use variables within that query when a macro tries to run it? If so, how!?

I've tried putting the SQL itself into several cells on a spreadsheet, altering the dates so that they a correct each time the query is run, concatenating the cells, turning the results into a variable and executing the SQL in a macro, but this fails at the line ".Refresh BackgroundQuery:=False". Could this method be adapted?

Splitting the query into two shorter ones doesn't appear to be an option as there a loads of 'or' statements in it, several of which overlap. This would cause some results to be duplicated, which I would like to avoid!

Please, please help me!!

TIA

tobyjuggler

 

[SkipVought]

tobyjuggler,
Please send me your code. I'll take a look at it. Skip,
Skip@theofficeexperts.com

 

[tobyjuggler]

Thanks for volunteering and apologies for my delayed response. I have sent you an e-mail. Hope it makes sense!