Thank you. That's a great first step.

If I could then get it to somehow pass through a value from a cell, it would
be perfect, but this really is a huge step forward.

Cheers,

Tom.

"NickHK" wrote:

I'm not sure how MS Query will handle your complexity....

When you are in MS Query, show the SQL. Then for any value that you want to
be a parameter, replace the value with a "?", no quotes.
You will prompted for a value for each; enter any value at this stage.
Click return to Excel
Now, if you right-click on the query, you will see the Parameters... option
available. Set the values/ranges for each, as required.

NickHK

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Nick,

Thanks so much for your answer.

I'm still learning this and don't know how to implement your solution.

How would I go about it? Do you know of any tips and tricks type pages
which concentrate on MS Query?

Cheers,

Tom.

"NickHK" wrote:

Given the complexity of the queries, if supported by the DB Engine, use
a
stored procedure and pass the dates as parameters.
Using ADO, it would be pretty straight forward.

NickHK

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
I've got a SQL query in an Excel spreadsheet.

Unfortunately, three lines are time dependent and need to be updated
on a
monthly basis.

The query follows:

/*************/

select Sub.AdviserDisplay,

sum(Sub.YTDExistingClients) as YTDExistingClients,

sum(Sub.YTDNewClients) as YTDNewClients,

sum(Sub.YTDAllClients) as YTDAllClients,

------------- SQL CUT ----------

order by Sub.AdviserDisplay

/***************/

Date entries such as 200701 (Jan 2007) would need to be adjusted.

As there are a lot of these and it would be easy to get wrong, I'd
like to
automate update of these.

Can I use VBA or a similar tool for this purpose.

Let's say the dates required are currently sat in cells somewhere in
the
workbook - for the sake of the example, Master!A1, B1 etc.

Thanks for any guidance or suggestions you can provide.

Cheers,

Tom.