Excel sheets linked to different msquerys 1

[Cpreston]

Hi

Just wondering if this would be possible.

If we have a workbook (Excel 2010) and there are for example 3 sheets. Each needs to have 3 different Msquerys. Is it possible to run all 3 query's from one command button.

So sheet 1 would come up with between prompt dates then refresh sheet1 data
Then date prompts for sheet 2 would then come up and refresh data on sheet 2

etc.....

Is this possible at all? If so could someone point me in the right direction with the coding please

Many thanks

 

[SkipVought]

Well the fact is that you have two parameters in each QT, so did you set up each parameter in all three QTs?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Yes I definitely set both parameters up in all three QTs

 

[Cpreston]

Hi

I have gone into each sheet and gone into the data connection properties and set date1 = =Sales!$A$2 and date2 = =Sales!$A$2 in all 3 QTs
I then ran the refresh data and I now get

Could not obtain parameter from reference (bad name or workbook not open) Proceed to prompt for values:

If I click OK and enter the date the same message comes up

I closed the spreadsheet and re-opened, it now asks for just one lot of dates but does not update the other 2 sheets

 

[SkipVought]

Please post your SQL as copied from MS Query SQL window.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

This is command text the Sales

SELECT OrderHeader.DateTimeCreated, OrderHeader.CustomerID, OrderHeader.CustomerRef
FROM "HTI Live".dbrderHeader OrderHeader
WHERE (OrderHeader.DateTimeCreated Between ? And ?)

This is the Sales date req

SELECT OrderHeader.DateTimeCreated, OrderHeader.DateRequired, OrderHeader.DeliveryAddress
FROM "HTI Live".dbrderHeader OrderHeader
WHERE (OrderHeader.DateTimeCreated Between ? And ?)

This is the Sales Invoice

SELECT OrderHeader.DateTimeCreated, OrderHeader.InvoiceAddress, OrderHeader.InvoiceAddressID
FROM "HTI Live".dbrderHeader OrderHeader
WHERE (OrderHeader.DateTimeCreated Between ? And ?)


Thanks

 

[Cpreston]

Also I should have mentione the sales one now asks for 2 dates.
It then refreshes the sales QT.

It does not refresh the other 2 QTs

If I go into the other QT, lets say sales date req
There is just one row showing.
If I click refresh it changes the date but only shows one row

and same for Sales invoice

 

[Cpreston]

Hi

Please excuse me I think I have been doing the incorrect thing, so apology's.

I run the macro, it refreshes the Sales sheet with all data (many rows)
it also changes the date in the other 2 QTs, but only shows one row and not a list od data.
There is no filter set on these sheets at all, and there should be more than one row showing.

I ran 01/01/2015 to 31/01/2015 and the date on the other 2 QTs just shows one row 29/01/2015

Any ideas please

Thanks

 

[SkipVought]

Well if all three have only one criteria...

WHERE (OrderHeader.DateTimeCreated Between ? And ?)

...then they should all return the same number of rows.

I'd strongly suspect that in the two QTs returning ONE ROW, that BOTH parameters are pointing to the start date or end date cell.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

Still having issues with this if anyone can help.

The cell references are definitely looking at the correct cell reference.

It lists all dates in the sales sheet but in the other 2 it just shows one row with the first (and earliest) date.
I have even recreated a new sheet with new querys and set the QTs the same. I still get the same issue.

So I am totally lost with how to achieve this. If anyone can help please let me know

Thanks

 

[SkipVought]

There's a chance that in the process of adding QueryTables, you actually have hidden QTs that appear to be messing with your application. I'd DELETE the sheets in question and start from scratch being ABSOLUTELY CERTAIN to add one and only one QT per sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

That is what I did, recreated a new spread sheet completely, recreated the query from fresh, added them in carefully one per sheet only.

Sales1, sales2 and sales3 named sheets.

Created the macro and named all sheets correct in the programming. triple checked the QT look at sales1!a2 for the update.

But still only get one row in sales2 and sales3 sheets.

Sorry but cannot see where it is incorrect.

 

[SkipVought]

What does this mean...

"look at sales1!a2 for the update."

I thought you has TWO date parameters???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi
In the table\parameters get the value from the following cell

sales2 sheet as this

date1 =sales1!$A$2
date2 =sales1!$A$2

sales3 sheet as the same

 

[SkipVought]

Well you state what's in sales2 & sales3, but what about sales1?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

If I do the same in sales1 sheet,

It then gives me could not obtain parameter from reference (bad name or workbook not open) proceed to prompt value and does this for all 3 sheets

 

[SkipVought]

All three queries must have the same criteria!!!

Your WHERE clause in each query is identical, using BETWEEN two different date parameters that in each QT must each reference THE SAME CELL, which I assume are ...

Sales1!A1 and Sales1!A2 or something like that.

If you say, BETWEEN the same two dates, what do you expect: the result will be different that if you say BETWEEN two DIFFERENT dates.

Am I missing something?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

Sorry about the late reply and Happy New year to everyone

What I am expecting is that it lists more dates for each sheet.

Sales1 shows all transactions between the two dates.
Sales2 and Sales3 only appears to go to the last date given in sales1 so just shows one row only for both sales2 and sales 3.

Thanks

 

[SkipVought]

Please upload your workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

File attached, thanks

 

[SkipVought]

What I see as your parameters makes absolutely no sense. I do not see how BETWEEN 11/2/2015 AND 11/2/2015 returns dates other than 11/2/2015???

I changed sales1 to what I would do, that is have 2 cells for the user to supply 2 dates, and then changed the parameters in all three so that date1 points to the FROM date and date2 points to the TO date.

Let me know if that produces the results you expect.

Another thing that may be significant (I don't really know) is that you are storing your query in a .dqy file, which I have never done in 20+ years of using MS Query. My queries are always stored with the workbook. I often change the SQL and/or the data source/connection string on the fly. Like I stated, don't know if that has any significance.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}