Error: Microsoft jet database engine does not recognize " as a va

H

HSL

I've been struggling with this error for a while now...hope someone out there
can help.

I have a form where the user would enter the parameters for a crosstab
query. The crosstab query works fine, and when I open the form and enter
parameters and then open the query, the query still works.

However, when I open the report, I get the following error:
Microsoft jet database engine does not recognize " as a valid fieldname or
expression.

So I am guessing that there is an issue in the VB module? The odd thing is
that this module worked in another version of my db, but unfortunately, I had
to re-do the db to change some tables and ended up copying and pasting the
module into my new db. Now the report doesn't work.

Could someone point me in the right direction as to where the problem might
lie? I feel like I've looked everywhere!!!

Here is my VB module:

Option Compare Database

' Constant for maximum number of columns qry_standard query would
' create - Currently 6 products.
Const conTotalColumns = 10

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
' For intX = intColumnCount + 2 To conTotalColumns
' Me("Col" + Format(intX)).Visible = False
' Next intX
'CAF 1/31/2005 Need to hide the DBox controls.
For intX = intColumnCount + 1 To 11
Me("DBox" + Format(intX)).Visible = False
Next intX


' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


'Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)



'Dim intX As Integer
'Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
'If Me.PrintCount = 1 Then
'lngRowTotal = 0

'For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
'lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
'lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" +
Format(intX))
'Next intX

' Put row total in text box in the "Detail" section.
'Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total. (DO NOT NEED GRAND
TOTAL ROW)
' lngReportTotal = lngReportTotal + lngRowTotal
'End If
'End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name

Next intX

' Make next available text box Totals heading. (DO NOT NEED TOTALS COLUMN)
' Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
'For intX = (intColumnCount + 2) To conTotalColumns
' Me("Head" + Format(intX)).Visible = False

'Next intX
'CAF 1/31/2005 Need to hide the HBox controls.
For intX = intColumnCount + 1 To 11
Me("HBox" + Format(intX)).Visible = False
Next intX


End Sub


Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation, "No
Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' frm_STD_RPT form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frm_keymeasrpt
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qry_standard")
' Set parameters for query based on values entered
' in frm_keymeasrpt.
qdf.Parameters("Forms!frm_keymeasrpt!TestProd1") _
= frm!TestProd1
qdf.Parameters("Forms!frm_keymeasrpt!TestProd2") _
= frm!TestProd2
qdf.Parameters("Forms!frm_keymeasrpt!TestProd3") _
= frm!TestProd3
qdf.Parameters("Forms!frm_keymeasrpt!TestProd4") _
= frm!TestProd4
qdf.Parameters("Forms!frm_keymeasrpt!TestProd5") _
= frm!TestProd5
qdf.Parameters("Forms!frm_keymeasrpt!TestProd6") _
= frm!TestProd6
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


'Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

'Dim intX As Integer
' DO NOT NEED REPORT FOOTER TOTALS
' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
'For intX = 2 To intColumnCount
'Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
'Next intX

' Put grand total in text box in report footer.
'Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
'For intX = intColumnCount + 2 To conTotalColumns
' Me("Tot" + Format(intX)).Visible = False
' Next intX

'End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub


'Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)

'Dim lns As Lines
'Dim lngHeight As Long
'Dim ctl As Control

'Set lns = New Lines
'Set lns.Parent = Me

'lns.Add 0

'For Each ctl In Me.Detail.Controls
'If ctl.Height > lngHeight Then lngHeight = ctl.Height
'If ctl.ControlType = acTextBox Then lns.Add (ctl.Left + ctl.Width)
'Next

'lns.Lengths = lngHeight

'lns.Draw
'Set lns = Nothing

'End Sub
 
D

Duane Hookom

If you are attempting to create a crosstab report with dynamic columns, the
solution you have chosen is slow and in-flexible. A better solution can be
found in the Crosstab.mdb found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

I expect your crosstab may be attempting to generate column headings on a
field that might contain a null value (no value). Try filter out all records
that might have a null in the field used as column headings.
 
H

HSL

If I try to filter out records with a null value, would I do that through the
Report Propoerties window or would it need to be done through the VB code?
 
H

HSL

Adding the filter in the crosstab query did not work - I still get the same
error when I try to open the report. Any other suggestions?
 
H

HSL

Here is the sql for my query.

PARAMETERS [Forms]![frm_keymeasrpt]![TestProd1] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd2] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd3] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd4] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd5] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd6] Text ( 255 );
TRANSFORM Max(Data.SCORE) AS MaxOfSCORE
SELECT SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
FROM SRVY_QUES INNER JOIN (Data INNER JOIN POP ON Data.POP_CODE =
POP.POP_CODE) ON SRVY_QUES.QUESCODE = Data.QUESCODE
WHERE (((Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd1] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd2] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd3] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd4] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd5] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd6]) AND
((([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd1] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd2] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd3] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd4] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd5] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd6]) Is Not Null))
GROUP BY SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
PIVOT Data.PRODNAME;
 
D

Duane Hookom

I still think you are possibly letting Null values in PRODNAME get through.

--
Duane Hookom
MS Access MVP


HSL said:
Here is the sql for my query.

PARAMETERS [Forms]![frm_keymeasrpt]![TestProd1] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd2] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd3] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd4] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd5] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd6] Text ( 255 );
TRANSFORM Max(Data.SCORE) AS MaxOfSCORE
SELECT SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
FROM SRVY_QUES INNER JOIN (Data INNER JOIN POP ON Data.POP_CODE =
POP.POP_CODE) ON SRVY_QUES.QUESCODE = Data.QUESCODE
WHERE (((Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd1] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd2] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd3] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd4] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd5] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd6]) AND
((([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd1] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd2] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd3] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd4] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd5] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd6]) Is Not Null))
GROUP BY SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
PIVOT Data.PRODNAME;


Duane Hookom said:
Share the SQL view of your crosstab query.
 
H

HSL

So, does that mean my query does not adequately address the null values?

Duane Hookom said:
I still think you are possibly letting Null values in PRODNAME get through.

--
Duane Hookom
MS Access MVP


HSL said:
Here is the sql for my query.

PARAMETERS [Forms]![frm_keymeasrpt]![TestProd1] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd2] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd3] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd4] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd5] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd6] Text ( 255 );
TRANSFORM Max(Data.SCORE) AS MaxOfSCORE
SELECT SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
FROM SRVY_QUES INNER JOIN (Data INNER JOIN POP ON Data.POP_CODE =
POP.POP_CODE) ON SRVY_QUES.QUESCODE = Data.QUESCODE
WHERE (((Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd1] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd2] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd3] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd4] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd5] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd6]) AND
((([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd1] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd2] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd3] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd4] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd5] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd6]) Is Not Null))
GROUP BY SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
PIVOT Data.PRODNAME;


Duane Hookom said:
Share the SQL view of your crosstab query.

--
Duane Hookom
MS Access MVP
--

Adding the filter in the crosstab query did not work - I still get the
same
error when I try to open the report. Any other suggestions?

:

In the crosstab query.

--
Duane Hookom
MS Access MVP


If I try to filter out records with a null value, would I do that
through
the
Report Propoerties window or would it need to be done through the VB
code?

:

If you are attempting to create a crosstab report with dynamic
columns,
the
solution you have chosen is slow and in-flexible. A better solution
can
be
found in the Crosstab.mdb found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

I expect your crosstab may be attempting to generate column
headings
on a
field that might contain a null value (no value). Try filter out
all
records
that might have a null in the field used as column headings.

--
Duane Hookom
MS Access MVP
--

I've been struggling with this error for a while now...hope
someone
out
there
can help.

I have a form where the user would enter the parameters for a
crosstab
query. The crosstab query works fine, and when I open the form
and
enter
parameters and then open the query, the query still works.

However, when I open the report, I get the following error:
Microsoft jet database engine does not recognize " as a valid
fieldname
or
expression.

So I am guessing that there is an issue in the VB module? The
odd
thing
is
that this module worked in another version of my db, but
unfortunately,
I
had
to re-do the db to change some tables and ended up copying and
pasting
the
module into my new db. Now the report doesn't work.

Could someone point me in the right direction as to where the
problem
might
lie? I feel like I've looked everywhere!!!

Here is my VB module:

Option Compare Database

' Constant for maximum number of columns qry_standard query
would
' create - Currently 6 products.
Const conTotalColumns = 10

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text
boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX -
1))
Next intX

' Hide unused text boxes in the "Detail" section.
' For intX = intColumnCount + 2 To conTotalColumns
' Me("Col" + Format(intX)).Visible = False
' Next intX
'CAF 1/31/2005 Need to hide the DBox controls.
For intX = intColumnCount + 1 To 11
Me("DBox" + Format(intX)).Visible = False
Next intX


' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


'Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)



'Dim intX As Integer
'Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
'If Me.PrintCount = 1 Then
'lngRowTotal = 0

'For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab
value),
' compute total for current row in the "Detail" section.
'lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
'lngRgColumnTotal(intX) = lngRgColumnTotal(intX) +
Me("Col"
+
Format(intX))
'Next intX

' Put row total in text box in the "Detail" section.
'Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total. (DO NOT
NEED
GRAND
TOTAL ROW)
' lngReportTotal = lngReportTotal + lngRowTotal
'End If
'End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section
retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer,
FormatCount
As
Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name

Next intX

' Make next available text box Totals heading. (DO NOT NEED
TOTALS
COLUMN)
' Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
'For intX = (intColumnCount + 2) To conTotalColumns
' Me("Head" + Format(intX)).Visible = False

'Next intX
'CAF 1/31/2005 Need to hide the HBox controls.
For intX = intColumnCount + 1 To 11
Me("HBox" + Format(intX)).Visible = False
Next intX


End Sub


Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.",
vbExclamation,
"No
Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria
entered
in
' frm_STD_RPT form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frm_keymeasrpt
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qry_standard")
' Set parameters for query based on values entered
' in frm_keymeasrpt.
qdf.Parameters("Forms!frm_keymeasrpt!TestProd1") _
= frm!TestProd1
qdf.Parameters("Forms!frm_keymeasrpt!TestProd2") _
= frm!TestProd2
qdf.Parameters("Forms!frm_keymeasrpt!TestProd3") _
= frm!TestProd3
qdf.Parameters("Forms!frm_keymeasrpt!TestProd4") _
= frm!TestProd4
qdf.Parameters("Forms!frm_keymeasrpt!TestProd5") _
= frm!TestProd5
qdf.Parameters("Forms!frm_keymeasrpt!TestProd6") _
= frm!TestProd6
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


'Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As
Integer)

'Dim intX As Integer
' DO NOT NEED REPORT FOOTER TOTALS
' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
'For intX = 2 To intColumnCount
'Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
'Next intX

' Put grand total in text box in report footer.
'Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
'For intX = intColumnCount + 2 To conTotalColumns
' Me("Tot" + Format(intX)).Visible = False
' Next intX

'End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

' Move to first record in recordset at the beginning of the
report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you
return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub


'Private Sub Detail_Print(Cancel As Integer, FormatCount As
Integer)

'Dim lns As Lines
'Dim lngHeight As Long
'Dim ctl As Control

'Set lns = New Lines
'Set lns.Parent = Me

'lns.Add 0

'For Each ctl In Me.Detail.Controls
'If ctl.Height > lngHeight Then lngHeight = ctl.Height
'If ctl.ControlType = acTextBox Then lns.Add (ctl.Left +
ctl.Width)
'Next

'lns.Lengths = lngHeight

'lns.Draw
'Set lns = Nothing

'End Sub
 
D

Duane Hookom

You are only checking to see if true or false value is not null. Try this:

WHERE
(
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd1] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd2] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd3] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd4] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd5] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd6]
) AND
([Data].[PRODNAME]) Is Not Null


--
Duane Hookom
MS Access MVP


HSL said:
So, does that mean my query does not adequately address the null values?

Duane Hookom said:
I still think you are possibly letting Null values in PRODNAME get
through.

--
Duane Hookom
MS Access MVP


HSL said:
Here is the sql for my query.

PARAMETERS [Forms]![frm_keymeasrpt]![TestProd1] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd2] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd3] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd4] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd5] Text ( 255 ),
[Forms]![frm_keymeasrpt]![TestProd6] Text ( 255 );
TRANSFORM Max(Data.SCORE) AS MaxOfSCORE
SELECT SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
FROM SRVY_QUES INNER JOIN (Data INNER JOIN POP ON Data.POP_CODE =
POP.POP_CODE) ON SRVY_QUES.QUESCODE = Data.QUESCODE
WHERE (((Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd1] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd2] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd3] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd4] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd5] Or
(Data.PRODNAME)=[Forms]![frm_keymeasrpt]![TestProd6]) AND
((([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd1] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd2] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd3] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd4] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd5] Or
([Data].[PRODNAME])=[Forms]![frm_keymeasrpt]![TestProd6]) Is Not Null))
GROUP BY SRVY_QUES.SRVY_FULL, SRVY_QUES.QUESDESCRIP, POP.POP_FULL
PIVOT Data.PRODNAME;


:

Share the SQL view of your crosstab query.

--
Duane Hookom
MS Access MVP
--

Adding the filter in the crosstab query did not work - I still get
the
same
error when I try to open the report. Any other suggestions?

:

In the crosstab query.

--
Duane Hookom
MS Access MVP


If I try to filter out records with a null value, would I do that
through
the
Report Propoerties window or would it need to be done through the
VB
code?

:

If you are attempting to create a crosstab report with dynamic
columns,
the
solution you have chosen is slow and in-flexible. A better
solution
can
be
found in the Crosstab.mdb found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

I expect your crosstab may be attempting to generate column
headings
on a
field that might contain a null value (no value). Try filter out
all
records
that might have a null in the field used as column headings.

--
Duane Hookom
MS Access MVP
--

I've been struggling with this error for a while now...hope
someone
out
there
can help.

I have a form where the user would enter the parameters for a
crosstab
query. The crosstab query works fine, and when I open the
form
and
enter
parameters and then open the query, the query still works.

However, when I open the report, I get the following error:
Microsoft jet database engine does not recognize " as a valid
fieldname
or
expression.

So I am guessing that there is an issue in the VB module? The
odd
thing
is
that this module worked in another version of my db, but
unfortunately,
I
had
to re-do the db to change some tables and ended up copying and
pasting
the
module into my new db. Now the report doesn't work.

Could someone point me in the right direction as to where the
problem
might
lie? I feel like I've looked everywhere!!!

Here is my VB module:

Option Compare Database

' Constant for maximum number of columns qry_standard query
would
' create - Currently 6 products.
Const conTotalColumns = 10

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report
totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into
text
boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) =
xtabCnulls(rstReport(intX -
1))
Next intX

' Hide unused text boxes in the "Detail" section.
' For intX = intColumnCount + 2 To conTotalColumns
' Me("Col" + Format(intX)).Visible = False
' Next intX
'CAF 1/31/2005 Need to hide the DBox controls.
For intX = intColumnCount + 1 To 11
Me("DBox" + Format(intX)).Visible = False
Next intX


' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


'Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)



'Dim intX As Integer
'Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
'If Me.PrintCount = 1 Then
'lngRowTotal = 0

'For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab
value),
' compute total for current row in the "Detail"
section.
'lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
'lngRgColumnTotal(intX) = lngRgColumnTotal(intX) +
Me("Col"
+
Format(intX))
'Next intX

' Put row total in text box in the "Detail" section.
'Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total. (DO NOT
NEED
GRAND
TOTAL ROW)
' lngReportTotal = lngReportTotal + lngRowTotal
'End If
'End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section
retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer,
FormatCount
As
Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name

Next intX

' Make next available text box Totals heading. (DO NOT NEED
TOTALS
COLUMN)
' Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
'For intX = (intColumnCount + 2) To conTotalColumns
' Me("Head" + Format(intX)).Visible = False

'Next intX
'CAF 1/31/2005 Need to hide the HBox controls.
For intX = intColumnCount + 1 To 11
Me("HBox" + Format(intX)).Visible = False
Next intX


End Sub


Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.",
vbExclamation,
"No
Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria
entered
in
' frm_STD_RPT form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frm_keymeasrpt
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qry_standard")
' Set parameters for query based on values entered
' in frm_keymeasrpt.
qdf.Parameters("Forms!frm_keymeasrpt!TestProd1") _
= frm!TestProd1
qdf.Parameters("Forms!frm_keymeasrpt!TestProd2") _
= frm!TestProd2
qdf.Parameters("Forms!frm_keymeasrpt!TestProd3") _
= frm!TestProd3
qdf.Parameters("Forms!frm_keymeasrpt!TestProd4") _
= frm!TestProd4
qdf.Parameters("Forms!frm_keymeasrpt!TestProd5") _
= frm!TestProd5
qdf.Parameters("Forms!frm_keymeasrpt!TestProd6") _
= frm!TestProd6
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab
query.
intColumnCount = rstReport.Fields.Count

End Sub


'Private Sub ReportFooter_Print(Cancel As Integer, PrintCount
As
Integer)

'Dim intX As Integer
' DO NOT NEED REPORT FOOTER TOTALS
' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
'For intX = 2 To intColumnCount
'Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
'Next intX

' Put grand total in text box in report footer.
'Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
'For intX = intColumnCount + 2 To conTotalColumns
' Me("Tot" + Format(intX)).Visible = False
' Next intX

'End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount
As
Integer)

' Move to first record in recordset at the beginning of the
report
' or when the report is restarted. (A report is restarted
when
' you print a report from Print Preview window, or when you
return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub


'Private Sub Detail_Print(Cancel As Integer, FormatCount As
Integer)

'Dim lns As Lines
'Dim lngHeight As Long
'Dim ctl As Control

'Set lns = New Lines
'Set lns.Parent = Me

'lns.Add 0

'For Each ctl In Me.Detail.Controls
'If ctl.Height > lngHeight Then lngHeight = ctl.Height
'If ctl.ControlType = acTextBox Then lns.Add (ctl.Left +
ctl.Width)
'Next

'lns.Lengths = lngHeight

'lns.Draw
'Set lns = Nothing

'End Sub
 

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

Top