report based on crosstab query

P

phleduc

I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the recordsource
accordingly.

To that purpose I would like to build a SQL string including the parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice) AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
 
S

SteveM

If your requirement is just to filter a query by some fields on a form, it
would be much simpler to just reference the fields in the query's criteria
e.g.
Forms!myForm.MyField

Steve
 
P

Pieter Wijnen

One option is

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice) AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE QryBookingsGRCstep1.proid=3
AND MyField = Forms!MyForm!MyControl
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate

Another is to use the Reports Filter & FilterOn Properties (in The Report
Open Event

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "MyField ='" & Forms!MyForm.MyControl.Value & "'" ' Text
Field
Me.Filter = "MyField =#" &
Format(Forms!MyForm.MyControl.Value,"yyyy-mm-dd") & "#" ' Date Field
Me.Filter = "MyField =" & Forms!MyForm.MyControl.Value ' Numeric Field
Me.FilterOn = True
End sub

HTH

Pieter


phleduc said:
I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the recordsource
accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property, QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS [Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
Click to expand...
 
P

phleduc

Hi Steve,

I tried that but because it is a crosstab query it does not recognize the
reference to the fields in the query,
even if i got them with the builder, these crosstab queries act different


SteveM said:
If your requirement is just to filter a query by some fields on a form, it
would be much simpler to just reference the fields in the query's criteria
e.g.
Forms!myForm.MyField

Steve

phleduc said:
I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the
recordsource
accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
Click to expand...
Click to expand...
 
P

phleduc

tried this as well, the problem the field I need to filter on is not in
crosstab query...
I really need a way to open the recordset using SQL statement, get no
records when I just replace the query by the SQL statement, even when not
filtering using
rst.Open strSQL where strSQL as I said is just the SQL statment of the query
even without filtering
drives me !#*$&*$%()


"Pieter Wijnen"
One option is

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE QryBookingsGRCstep1.proid=3
AND MyField = Forms!MyForm!MyControl
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate

Another is to use the Reports Filter & FilterOn Properties (in The Report
Open Event

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "MyField ='" & Forms!MyForm.MyControl.Value & "'" ' Text
Field
Me.Filter = "MyField =#" &
Format(Forms!MyForm.MyControl.Value,"yyyy-mm-dd") & "#" ' Date Field
Me.Filter = "MyField =" & Forms!MyForm.MyControl.Value ' Numeric Field
Me.FilterOn = True
End sub

HTH

Pieter


phleduc said:
I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the
recordsource accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property, QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS [Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
Click to expand...
Click to expand...
 
P

Pieter Wijnen

Then

Dim Pos As Long

Pos = Instr(StrSQL,"GROUP BY") -1
strSQL = Left(strSQL,Pos ) & " AND MyField=Forms!MyForm!MyControl " &
Mid(strSQL,Pos+1)

HTH

Pieter




phleduc said:
tried this as well, the problem the field I need to filter on is not in
crosstab query...
I really need a way to open the recordset using SQL statement, get no
records when I just replace the query by the SQL statement, even when not
filtering using
rst.Open strSQL where strSQL as I said is just the SQL statment of the
query even without filtering
drives me !#*$&*$%()


"Pieter Wijnen"
One option is

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE QryBookingsGRCstep1.proid=3
AND MyField = Forms!MyForm!MyControl
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate

Another is to use the Reports Filter & FilterOn Properties (in The Report
Open Event

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "MyField ='" & Forms!MyForm.MyControl.Value & "'" ' Text
Field
Me.Filter = "MyField =#" &
Format(Forms!MyForm.MyControl.Value,"yyyy-mm-dd") & "#" ' Date Field
Me.Filter = "MyField =" & Forms!MyForm.MyControl.Value ' Numeric Field
Me.FilterOn = True
End sub

HTH

Pieter


phleduc said:
I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from
a
form, grab a couple of parameters of that form and filter the
recordsource accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property, QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS [Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
Click to expand...
Click to expand...
Click to expand...
 
S

SteveM

I had to try it to prove it to myself, you are right! I'd never noticed that
before...

Well you could construct the SQL and reset the QueryDef's SQL with that.
That way, your report will still be bound to the query and you don't have to
use a recordset.
Nothing wrong with using a recordset if you want to though...

Steve

phleduc said:
Hi Steve,

I tried that but because it is a crosstab query it does not recognize the
reference to the fields in the query,
even if i got them with the builder, these crosstab queries act different


SteveM said:
If your requirement is just to filter a query by some fields on a form, it
would be much simpler to just reference the fields in the query's criteria
e.g.
Forms!myForm.MyField

Steve

phleduc said:
I am trying to build a report based on a crosstab query, to that purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report from a
form, grab a couple of parameters of that form and filter the
recordsource
accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat, Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
Click to expand...
Click to expand...
Click to expand...
 
P

phleduc

Decided to solve it by building a tempary table with the filtered data, that
is build when clicking the button to view the report
 
P

phleduc

I solved it finally by building a Query with paramaters that builds a
temporary table used for the crosstab.
I am not sure this is the best solution but it does work
SteveM said:
I had to try it to prove it to myself, you are right! I'd never noticed
that
before...

Well you could construct the SQL and reset the QueryDef's SQL with that.
That way, your report will still be bound to the query and you don't have
to
use a recordset.
Nothing wrong with using a recordset if you want to though...

Steve

phleduc said:
Hi Steve,

I tried that but because it is a crosstab query it does not recognize the
reference to the fields in the query,
even if i got them with the builder, these crosstab queries act different


SteveM said:
If your requirement is just to filter a query by some fields on a form,
it
would be much simpler to just reference the fields in the query's
criteria
e.g.
Forms!myForm.MyField

Steve

:

I am trying to build a report based on a crosstab query, to that
purpose
(according to Access developers handbook)
I use the reports recordsource (a crosstab query) to get the recordset
needed to bound my (unbound) controls on the "on open" event of of the
report

This works fine but my problem is that I need to launch the report
from a
form, grab a couple of parameters of that form and filter the
recordsource
accordingly.

To that purpose I would like to build a SQL string including the
parameters.

How can I modify the code below to make this work?
let say strSQL =

TRANSFORM Sum(QryBookingsGRCstep1.bkiQuantity) AS SumOfbkiQuantity
SELECT QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode AS
property,
QryBookingsGRCstep1.bkgID AS bkg, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat AS Cat,
Avg(QryBookingsGRCstep1.rbkUnitPrice)
AS
[Avg Price]
FROM QryBookingsGRCstep1
WHERE (((QryBookingsGRCstep1.proid)=3))
GROUP BY QryBookingsGRCstep1.Booking, QryBookingsGRCstep1.proCode,
QryBookingsGRCstep1.bkgID, QryBookingsGRCstep1.Date,
QryBookingsGRCstep1.cbgCat
ORDER BY QryBookingsGRCstep1.proCode, QryBookingsGRCstep1.bkgID,
QryBookingsGRCstep1.rbkDate
PIVOT QryBookingsGRCstep1.rbkDate;
WHERE


Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable
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

report based on crostab query with parameters 1
Form based on crosstab query 1
report using a subreport based on a crosstab query 5
Crosstab Query Questions 5
report based on crosstab 2
Report Question... 11
Report based on a crosstab query 2
List Box to create a Report based on crosstab query 6

Top