Re: Can't make a report from crosstab query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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





"Duane Hookom" <DuaneAtNoSpanHookomDotNet> skrev i meddelandet
news:OE68GPFsGHA.1288@xxxxxxxxxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: reading from txt file into table
    ... .Fields.Append fld ... Dim tdf As DAO.TableDef ... Dim lngInputFile As Long ... Then you can create a query into the text file, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Is there a way to list the tables/queries that comprise a single query? ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ...
    (microsoft.public.access.modulesdaovba)
  • Re: query after insert fails
    ... event in the form open code for the form that uses the query. ... Based on the selection criteria the search then inserts ... Dim IntPos1 As Integer ... Dim oCmd As Command ...
    (microsoft.public.access.modulesdaovba)
  • Re: using VBA to print out lists of query tables and fields
    ... then you can probably solve the problem you were having with Dim ... fld As Field by specifying the DAO object library, ... Although the graphical query ... > Dim qdf As QueryDef, fld As Variant 'didn't work ...
    (microsoft.public.access.queries)
  • Re: using VBA to print out lists of query tables and fields
    ... then you can probably solve the problem you were having with Dim ... fld As Field by specifying the DAO object library, ... Although the graphical query ... > Dim qdf As QueryDef, fld As Variant 'didn't work ...
    (microsoft.public.access.modulesdaovba)