Can't make a report from crosstab query

[Rolf Rosenquist]

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set with
code for a crosstab query?


/ Rolf

 

[Marshall Barton]

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set with
code for a crosstab query?

Not a bug. Form references only work when the form is open.
The wizard is opening the query to determine the fields that
should go on the report.

 

[Duane Hookom]

Share your SQL view of the crosstab.

Did you enter all possible column headings into the Column Headings property
of the crosstab?

Is your "myform" open with a value in "mycontrol"?

 

[Rolf Rosenquist]

I have the form open and populated with data and still get this result.
/ Rolf

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set with
code for a crosstab query?

Not a bug. Form references only work when the form is open.
The wizard is opening the query to determine the fields that
should go on the report.

 

[Rolf Rosenquist]

Yes, the column and row headings are specified
And the form is open and populated.
The SQL view is

TRANSFORM Sum(Utleverans.Summa) AS SummaförSumma
SELECT Order.Fraktsedel AS Waybill, Mottagare.Namn AS Consignee,
Mottagare.Adress AS Address, Mottagare.Postnr AS Postal,
Mottagare.Postadress AS [Place of del], Order.KundNr, Sum(Utleverans.Summa)
AS [Sum]
FROM Mottagare INNER JOIN (((Kunder INNER JOIN Lager ON Kunder.KundNr =
Lager.KundNr) INNER JOIN [Order] ON Kunder.KundNr = Order.KundNr) INNER JOIN
Utleverans ON (Order.OrderNr = Utleverans.OrderNr) AND (Lager.ArtikelNr =
Utleverans.ArtikelNr)) ON Mottagare.MottagareNr = Order.MottagareNr
GROUP BY Order.Fraktsedel, Mottagare.Namn, Mottagare.Adress,
Mottagare.Postnr, Mottagare.Postadress, Order.KundNr, Order.LevDatum
PIVOT Lager.KategoriNr;

/ Rolf

Share your SQL view of the crosstab.

Did you enter all possible column headings into the Column Headings property
of the crosstab?

Is your "myform" open with a value in "mycontrol"?


--
Duane Hookom
MS Access MVP

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only
the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase
them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not
occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set
with
code for a crosstab query?


/ Rolf

 

[Rolf Rosenquist]

Even more strange is that if I make a new normal query, and specify the
crosstab query as source and set the condition to the form as described I
get an error that says, if I translate to english, 'Cannot identify
[forms]![myform]![mycontrol] as a valid field name or expression in
Microsoft Jet.' And if I manually write a condition in this normal query it
works. Without condition all the records are shown.

If I do the same and instad use the table itself as source, it works with
the form control as selection. - But not with crosstab query as source. I
find that very strange.

/ Rolf

Share your SQL view of the crosstab.

Did you enter all possible column headings into the Column Headings property
of the crosstab?

Is your "myform" open with a value in "mycontrol"?


--
Duane Hookom
MS Access MVP

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only
the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase
them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not
occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set
with
code for a crosstab query?


/ Rolf

 

[Duane Hookom]

You did not "enter all possible column headings into the Column Headings
property". I would expect to see "PIVOT Lager.KategoriNr In (......);" at
the end of your sql syntax.

I don't see any reference to " [forms]![myform]![mycontrol]" in the SQL view
and also don't see any parameter data types in the sql which would start
with: "PARAMETERS [forms]![myform]![mycontrol] Date/Time;"


--
Duane Hookom
MS Access MVP

Yes, the column and row headings are specified
And the form is open and populated.
The SQL view is

TRANSFORM Sum(Utleverans.Summa) AS SummaförSumma
SELECT Order.Fraktsedel AS Waybill, Mottagare.Namn AS Consignee,
Mottagare.Adress AS Address, Mottagare.Postnr AS Postal,
Mottagare.Postadress AS [Place of del], Order.KundNr,
Sum(Utleverans.Summa)
AS [Sum]
FROM Mottagare INNER JOIN (((Kunder INNER JOIN Lager ON Kunder.KundNr =
Lager.KundNr) INNER JOIN [Order] ON Kunder.KundNr = Order.KundNr) INNER
JOIN
Utleverans ON (Order.OrderNr = Utleverans.OrderNr) AND (Lager.ArtikelNr =
Utleverans.ArtikelNr)) ON Mottagare.MottagareNr = Order.MottagareNr
GROUP BY Order.Fraktsedel, Mottagare.Namn, Mottagare.Adress,
Mottagare.Postnr, Mottagare.Postadress, Order.KundNr, Order.LevDatum
PIVOT Lager.KategoriNr;

/ Rolf

Share your SQL view of the crosstab.

Did you enter all possible column headings into the Column Headings property
of the crosstab?

Is your "myform" open with a value in "mycontrol"?


--
Duane Hookom
MS Access MVP

Have made a qrosstab query that works fine. Have also been able to use the
wizard to generate a report from it. Then I got a problem when I tried to
let the query read the WHERE conditions from a form. I need to get only
the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the
fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase
them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not
occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set
with
code for a crosstab query?


/ Rolf

 

[Rolf Rosenquist]

No sorry, I did not by mistake. The SQL I sent was the new one that works
without the selection. Of course I should have sent the faulty one. It was
not meant to spoil any time from you. Below I send the one that does not
work.

In my first mail I said "myform" and "mycontrol" as general names. Here they
are called "utskrifter" and "kundval"
You said that you expected someting after "PIVOT Lager.KategoriNr In
(......);" There is still nothing of that, and I don't know what and how to
put something there. This is generated by Access in the automated pane (QBE
or something alike?). Maybe you have found something here that could explain
why it doesn't work...?

Well, it works in the meaning that I now get the intended selection, when
running the crosstab query itself. But it doesn't work when I build a new
query from this result, that then should give the report. And it is
indicated, when I for instance try to create a report direct from the
crosstab query with help of the wizard.

PARAMETERS [forms]![utskrifter]![kundval] Long;
TRANSFORM Sum(Utleverans.Summa) AS SummaförSumma
SELECT Order.Fraktsedel AS Waybill, Mottagare.Namn AS Consignee,
Mottagare.Adress AS Address, Mottagare.Postnr AS Postal,
Mottagare.Postadress AS [Place of del], Order.KundNr, Sum(Utleverans.Summa)
AS [Sum]
FROM Mottagare INNER JOIN (((Kunder INNER JOIN Lager ON Kunder.KundNr =
Lager.KundNr) INNER JOIN [Order] ON Kunder.KundNr = Order.KundNr) INNER JOIN
Utleverans ON (Order.OrderNr = Utleverans.OrderNr) AND (Lager.ArtikelNr =
Utleverans.ArtikelNr)) ON Mottagare.MottagareNr = Order.MottagareNr
WHERE (((Order.KundNr)=[forms]![utskrifter]![kundval]))
GROUP BY Order.Fraktsedel, Mottagare.Namn, Mottagare.Adress,
Mottagare.Postnr, Mottagare.Postadress, Order.KundNr, Order.LevDatum
PIVOT Lager.KategoriNr;


/ Rolf

You did not "enter all possible column headings into the Column Headings
property". I would expect to see "PIVOT Lager.KategoriNr In (......);" at
the end of your sql syntax.

I don't see any reference to " [forms]![myform]![mycontrol]" in the SQL view
and also don't see any parameter data types in the sql which would start
with: "PARAMETERS [forms]![myform]![mycontrol] Date/Time;"


--
Duane Hookom
MS Access MVP

Yes, the column and row headings are specified
And the form is open and populated.
The SQL view is

TRANSFORM Sum(Utleverans.Summa) AS SummaförSumma
SELECT Order.Fraktsedel AS Waybill, Mottagare.Namn AS Consignee,
Mottagare.Adress AS Address, Mottagare.Postnr AS Postal,
Mottagare.Postadress AS [Place of del], Order.KundNr,
Sum(Utleverans.Summa)
AS [Sum]
FROM Mottagare INNER JOIN (((Kunder INNER JOIN Lager ON Kunder.KundNr =
Lager.KundNr) INNER JOIN [Order] ON Kunder.KundNr = Order.KundNr) INNER
JOIN
Utleverans ON (Order.OrderNr = Utleverans.OrderNr) AND (Lager.ArtikelNr =
Utleverans.ArtikelNr)) ON Mottagare.MottagareNr = Order.MottagareNr
GROUP BY Order.Fraktsedel, Mottagare.Namn, Mottagare.Adress,
Mottagare.Postnr, Mottagare.Postadress, Order.KundNr, Order.LevDatum
PIVOT Lager.KategoriNr;

/ Rolf

Share your SQL view of the crosstab.

Did you enter all possible column headings into the Column Headings property
of the crosstab?

Is your "myform" open with a value in "mycontrol"?


--
Duane Hookom
MS Access MVP

Have made a qrosstab query that works fine. Have also been able to

use
the

wizard to generate a report from it. Then I got a problem when I

tried
to

let the query read the WHERE conditions from a form. I need to get only
the
records from a certain customer on a special day. So I used the format
[forms]![myform]![mycontrol]

It works when I only look at the result of the crosstab query, but then
something goes wrong, and I can confirm this fault, if I try to make a
report with help of the wizard. In the stage where to select the
fields,
there aren't any at all. It is just blank. But if I instead as a test put
those conditions in manually, the fields are there in the wizard.

I think Access in a way so to say, destroys the query when using those
references to a form, because it will never work again, even if I erase
them
to the original state, the wizard won't recognize any field for that
crosstab query any more. But with normal querys this problem does not
occur.

Is this possibly a flaw in Access? Can WHERE conditions instead be set
with
code for a crosstab query?


/ Rolf

 

[Rolf Rosenquist]

I said in my last answer to you:

But it doesn't work when I build a new
query from this result, that then should give the report. And it is
indicated, when I for instance try to create a report direct from the
crosstab query with help of the wizard.

Now I have found a little bit more. When the normal query should be
generated by code, I notice once more, that the fields in the crosstab query
don't appear. That's the same error as in the wizard, because when stepping
through the code, it jumps right over the "For Each fld in qdf.Fields" and
when holding the cursor over the rs, qdf and fld in the Dim statements it
says "=Nothing" .

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim I As Integer

Set db = CurrentDb
Stop

'*** Korsfrågan ***
Set qdf = db.QueryDefs("xfrManifest")
indexx = 0
For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx &
", "
ReportLabel(indexx) = fld.Name
End If
indexx = indexx + 1
Next fld
............

So it seems to me that the crosstab query gets corrupt each time I use a
selection based on a control in a form. Even when I remove it, so that there
is no selection at all, I still get the same result. The only way to make it
run (without selections or with selection manually input as with KundNr=2)
is to rewrite it from scratch. That I think, could perhaps be a flaw in
Access...?

/ Rolf

You did not "enter all possible column headings into the Column Headings
property". I would expect to see "PIVOT Lager.KategoriNr In (......);" at
the end of your sql syntax.

I don't see any reference to " [forms]![myform]![mycontrol]" in the SQL view
and also don't see any parameter data types in the sql which would start
with: "PARAMETERS [forms]![myform]![mycontrol] Date/Time;"

 

[Duane Hookom]

Can you pre-identify every column name you expect your crosstab to generate?
If so, use the Column Headings property of the crosstab to enter all the
heading values. For instance, in Northwind, you could create a crosstab
like:

TRANSFORM Count(Employees.EmployeeID) AS CountOfEmployeeID
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
PIVOT Employees.TitleOfCourtesy In ("Dr.","Mr.","Mrs.","Ms.");

It looks however like you are attempting to do something unusual/dynamic
with the query on-the-fly. Are you attempting to implement the "ugly"
crosstab report found in the Solutions.MDB?

--
Duane Hookom
MS Access MVP

I said in my last answer to you:

But it doesn't work when I build a new
query from this result, that then should give the report. And it is
indicated, when I for instance try to create a report direct from the
crosstab query with help of the wizard.

Now I have found a little bit more. When the normal query should be
generated by code, I notice once more, that the fields in the crosstab
query
don't appear. That's the same error as in the wizard, because when
stepping
through the code, it jumps right over the "For Each fld in qdf.Fields" and
when holding the cursor over the rs, qdf and fld in the Dim statements it
says "=Nothing" .

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim I As Integer

Set db = CurrentDb
Stop

'*** Korsfrågan ***
Set qdf = db.QueryDefs("xfrManifest")
indexx = 0
For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx
&
", "
ReportLabel(indexx) = fld.Name
End If
indexx = indexx + 1
Next fld
...........

So it seems to me that the crosstab query gets corrupt each time I use a
selection based on a control in a form. Even when I remove it, so that
there
is no selection at all, I still get the same result. The only way to make
it
run (without selections or with selection manually input as with KundNr=2)
is to rewrite it from scratch. That I think, could perhaps be a flaw in
Access...?

/ Rolf

You did not "enter all possible column headings into the Column Headings
property". I would expect to see "PIVOT Lager.KategoriNr In (......);" at
the end of your sql syntax.

I don't see any reference to " [forms]![myform]![mycontrol]" in the SQL view
and also don't see any parameter data types in the sql which would start
with: "PARAMETERS [forms]![myform]![mycontrol] Date/Time;"

 

[Rolf Rosenquist]

Yes, I agree that it is both unusual and dynamic. But now it is solved. The
first columns are always the same and the last 6 are dynamic and not the
same or not even the same number of columns from one report to another.

As you suggested I could define the two needed, customer number and date of
delivery, in the code that generate the normal query before the report. I
put those selections in the SQL string and then it worked.

That means that the crosstab query shows all the records with these fields
and the selections come one step later. This way the references to the form
does not generate any errors.

Thank you very much for your comments that really helped me to make it work.
/ Rolf

Can you pre-identify every column name you expect your crosstab to generate?
If so, use the Column Headings property of the crosstab to enter all the
heading values. For instance, in Northwind, you could create a crosstab
like:

TRANSFORM Count(Employees.EmployeeID) AS CountOfEmployeeID
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
PIVOT Employees.TitleOfCourtesy In ("Dr.","Mr.","Mrs.","Ms.");

It looks however like you are attempting to do something unusual/dynamic
with the query on-the-fly. Are you attempting to implement the "ugly"
crosstab report found in the Solutions.MDB?

--
Duane Hookom
MS Access MVP

I said in my last answer to you:

But it doesn't work when I build a new
query from this result, that then should give the report. And it is
indicated, when I for instance try to create a report direct from the
crosstab query with help of the wizard.

Now I have found a little bit more. When the normal query should be
generated by code, I notice once more, that the fields in the crosstab
query
don't appear. That's the same error as in the wizard, because when
stepping
through the code, it jumps right over the "For Each fld in qdf.Fields" and
when holding the cursor over the rs, qdf and fld in the Dim statements it
says "=Nothing" .

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim I As Integer

Set db = CurrentDb
Stop

'*** Korsfrågan ***
Set qdf = db.QueryDefs("xfrManifest")
indexx = 0
For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx
&
", "
ReportLabel(indexx) = fld.Name
End If
indexx = indexx + 1
Next fld
...........

So it seems to me that the crosstab query gets corrupt each time I use a
selection based on a control in a form. Even when I remove it, so that
there
is no selection at all, I still get the same result. The only way to make
it
run (without selections or with selection manually input as with KundNr=2)
is to rewrite it from scratch. That I think, could perhaps be a flaw in
Access...?

/ Rolf

You did not "enter all possible column headings into the Column Headings
property". I would expect to see "PIVOT Lager.KategoriNr In (......);" at
the end of your sql syntax.

I don't see any reference to " [forms]![myform]![mycontrol]" in the SQL view
and also don't see any parameter data types in the sql which would start
with: "PARAMETERS [forms]![myform]![mycontrol] Date/Time;"