Managed it in VBQ and Query.

Thanks for all your help, Nick.

Tom.

"NickHK" wrote:

Yes, well MS Query is relatively <simple.

Time to bite the bullet and look at Stored Procedures
- depends on your DB

and/or ADO.
- http://support.microsoft.com/kb/257819

NickHK

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

Get the message: "Parameters are not allowed in queries that can't be
displayed graphically."

Sorry.


"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.