Pivot Table from Access through MS Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create an Excel pivot table from a simple Access query with 2
parameters, using MS Query. (start date, end date)
I have been able to create the pivot table from the query easily without the
parameters and I have been able to run the query by itself with the
parameters, but when I try to run the pivot off the edited query with
parameters, I get an error message that parameters are not allowed.
Is there some way around this?
Thank you.
 
You could create a pivot table from the query without parameters. Then
use code similar to the following, to prompt for a date range:

'====================================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")

ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Test.mdb`" _
& ".qryInvDate WHERE (((qryInvDate.InvDate) Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"

End Sub
'==============================
 
Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson
 
Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================
 
Now I am getting the prompts properly and it starts to run and up pops an
error message "400" with no other explanation.


Debra Dalgleish said:
Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================
Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson

:
Click to expand...
Click to expand...
 
I see you have back-quotes (`). Are you sure then shouldn't be forward quotes
( ' = apostrohe's)

Now I am getting the prompts properly and it starts to run and up pops an
error message "400" with no other explanation.


Debra Dalgleish said:
Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================
Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson

:


You could create a pivot table from the query without parameters. Then
use code similar to the following, to prompt for a date range:

'====================================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")

ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Test.mdb`" _
& ".qryInvDate WHERE (((qryInvDate.InvDate) Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"

End Sub
'==============================

AllyOop wrote:

I am trying to create an Excel pivot table from a simple Access query with 2
parameters, using MS Query. (start date, end date)
I have been able to create the pivot table from the query easily without the
parameters and I have been able to run the query by itself with the
parameters, but when I try to run the pivot off the edited query with
parameters, I get an error message that parameters are not allowed.
Is there some way around this?
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Ok. I changed the quotes you mentioned and this time I got a
Run-time error '1004'

Here is my revised code:

Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM 'R:\Customer Service.mdb'" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_date)Between
#" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub

Myrna Larson said:
I see you have back-quotes (`). Are you sure then shouldn't be forward quotes
( ' = apostrohe's)

Now I am getting the prompts properly and it starts to run and up pops an
error message "400" with no other explanation.


Debra Dalgleish said:
Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================

AllyOop wrote:
Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson

:


You could create a pivot table from the query without parameters. Then
use code similar to the following, to prompt for a date range:

'====================================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")

ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Test.mdb`" _
& ".qryInvDate WHERE (((qryInvDate.InvDate) Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"

End Sub
'==============================

AllyOop wrote:

I am trying to create an Excel pivot table from a simple Access query with 2
parameters, using MS Query. (start date, end date)
I have been able to create the pivot table from the query easily without the
parameters and I have been able to run the query by itself with the
parameters, but when I try to run the pivot off the edited query with
parameters, I get an error message that parameters are not allowed.
Is there some way around this?
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
In this line:

& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_date)Between

there should be a space before Between.

Also, is your database in the R directory, or is it in a folder in the R
directory?
Ok. I changed the quotes you mentioned and this time I got a
Run-time error '1004'

Here is my revised code:

Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM 'R:\Customer Service.mdb'" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_date)Between
#" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub

:

I see you have back-quotes (`). Are you sure then shouldn't be forward quotes
( ' = apostrohe's)

Now I am getting the prompts properly and it starts to run and up pops an
error message "400" with no other explanation.


:


Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================

AllyOop wrote:

Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson

:



You could create a pivot table from the query without parameters. Then
use code similar to the following, to prompt for a date range:

'====================================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")

ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Test.mdb`" _
& ".qryInvDate WHERE (((qryInvDate.InvDate) Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"

End Sub
'==============================

AllyOop wrote:


I am trying to create an Excel pivot table from a simple Access query
Click to expand...
with 2
parameters, using MS Query. (start date, end date)
I have been able to create the pivot table from the query easily without
the

parameters and I have been able to run the query by itself with the
parameters, but when I try to run the pivot off the edited query with
parameters, I get an error message that parameters are not allowed.
Is there some way around this?
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
I put the space in and am still getting the same error message.
Yes, the database is in the R directory directly and not in a folder.

Debra Dalgleish said:
In this line:

& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_date)Between

there should be a space before Between.

Also, is your database in the R directory, or is it in a folder in the R
directory?
Ok. I changed the quotes you mentioned and this time I got a
Run-time error '1004'

Here is my revised code:

Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM 'R:\Customer Service.mdb'" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_date)Between
#" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub

:

I see you have back-quotes (`). Are you sure then shouldn't be forward quotes
( ' = apostrohe's)

On Tue, 19 Oct 2004 12:27:04 -0700, "AllyOop"


Now I am getting the prompts properly and it starts to run and up pops an
error message "400" with no other explanation.


:


Square brackets should work:

'==================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\customer service.mdb.mdb`" _
& ".[Y-Delivery Status] WHERE ((([Y-Delivery Status].Dlvry_Status)
Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"
End Sub
'=================================

AllyOop wrote:

Thank you very much!
This seems like it should work, but isn't.
Then again, I am a novice at code writing so maybe my syntax isn't quite
right.
How do you account for spaces?
EX. my db is customer service.mdb
My Access query is Y-Delivery Status
and the date field I am using parameters in is Dlvry_Status.
Thanks again,
Allyson

:



You could create a pivot table from the query without parameters. Then
use code similar to the following, to prompt for a date range:

'====================================
Sub RefreshCommandText()
'refresh pivot table command text,
'including prompt for date range
Dim dtmStart As Date
Dim dtmEnd As Date
dtmStart = InputBox("Start Date")
dtmEnd = InputBox("End Date")

ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Test.mdb`" _
& ".qryInvDate WHERE (((qryInvDate.InvDate) Between #" _
& dtmStart & "# And #" & dtmEnd & "#))"

End Sub
'==============================

AllyOop wrote:


I am trying to create an Excel pivot table from a simple Access query

with 2

parameters, using MS Query. (start date, end date)
I have been able to create the pivot table from the query easily without

the

parameters and I have been able to run the query by itself with the
parameters, but when I try to run the pivot off the edited query with
parameters, I get an error message that parameters are not allowed.
Is there some way around this?
Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access #Error & Excel Pivot 0
Access query not showing as sources for pivot table 2
Issue with pivot table 1
PIVOT TABLE 3
Parameter query conversion error on date 1
Linking parameter query from Access to pivot table in Excel 2
Refreshing Pivot Table on a protected spreadsheet tab 1
using access for pivot table source 1

Back
Top