Hi,

I've searched this site but I can't see a direct answer to what I am trying
to do.

I've set up a Pivot Table that accesses data from MS Access via MS Query.
the criteria is set to get records with a Dept = 800.

I can edit the query manually but how do I change this to Dept = 810 via VBA ?

The query in MS Query is as follows:

SELECT tbl_Comm_Data.RCV_RETL, tbl_Comm_Data.RCV_MARGIN
FROM `G:\00_CEN\Oth\Inventory Planning Reports\POL\POL`.tbl_Comm_Data
tbl_Comm_Data
WHERE (tbl_Comm_Data.DEPT_NO='800')


--
Andrew
211008

G'day Andrew

Rather than Making this part of your Query a constant

WHERE (tbl_Comm_Data.DEPT_NO='800')

Make the Query reference the Dept_No via a control (User manually inputs the
Dept_No) or combo (Select from list)

Try something like this in this part of the code:

WHERE (tbl_Comm_Data.DEPT_NO= &"Forms!YourForm!YourControl"&)

The Syntax may not be quite correct, hopefully it will point you in the
right direct.

HTH
Mark.

Hi,

I would record the creation of the initial pivot table. Excel will create
an SQL statement for you in VBA. You can easily modify that code to pick of
entries for any variable within the spreadsheet or via a user form. When you
record it post a copy of the VBA code and we can help you with specific
answers.

--
Thanks,
Shane Devenshire



"Andrew" wrote:

Hi,

I've searched this site but I can't see a direct answer to what I am trying
to do.

I've set up a Pivot Table that accesses data from MS Access via MS Query.
the criteria is set to get records with a Dept = 800.

I can edit the query manually but how do I change this to Dept = 810 via VBA ?

The query in MS Query is as follows:

SELECT tbl_Comm_Data.RCV_RETL, tbl_Comm_Data.RCV_MARGIN
FROM `G:\00_CEN\Oth\Inventory Planning Reports\POL\POL`.tbl_Comm_Data
tbl_Comm_Data
WHERE (tbl_Comm_Data.DEPT_NO='800')


--
Andrew
211008

Hi NoodNutt & Shane,

I think I've used NudeNutt's suggestion in a roundabout way. In Access I
created a new table called tbl_Dept_No. It has one field with one record in
it. I edited the query and added this table to the query with a join on the
Dept_No fields.

Now I can enter the required department number in the Access table and then
go to my Pivot Table, refresh it and get the required results.

I can update the table via SQL so problem solved.


Shane,

I did try to record the creation before you replied to me. I wasn't able to
change it to edit the query though.




--
Andrew


"ShaneDevenshire" wrote:

Hi,

I would record the creation of the initial pivot table. Excel will create
an SQL statement for you in VBA. You can easily modify that code to pick of
entries for any variable within the spreadsheet or via a user form. When you
record it post a copy of the VBA code and we can help you with specific
answers.

--
Thanks,
Shane Devenshire



"Andrew" wrote:

Hi,

I've searched this site but I can't see a direct answer to what I am trying
to do.

I've set up a Pivot Table that accesses data from MS Access via MS Query.
the criteria is set to get records with a Dept = 800.

I can edit the query manually but how do I change this to Dept = 810 via VBA ?

The query in MS Query is as follows:

SELECT tbl_Comm_Data.RCV_RETL, tbl_Comm_Data.RCV_MARGIN
FROM `G:\00_CEN\Oth\Inventory Planning Reports\POL\POL`.tbl_Comm_Data
tbl_Comm_Data
WHERE (tbl_Comm_Data.DEPT_NO='800')


--
Andrew
211008

Happy to help Andrew

Thx for the feedback.

Regards
Mark.