Microsoft Jet Database Engine Does Not Recognize " ...

[EMoore12]

I've created a dynamic crosstab report based off a crosstab query. I needed to sort the columns in a certain order and not alphabetically. In the query, I've prefixed a two-digit number before each column name to make the columns sort numerically. I've added additional code to the report to strip the first two digits of the columns. When I try to run the report I received the following message: "The Microsoft Jet database engine does not recognize " as a valid field name or expression." Here is an example of what the code looks like:

Dim qdf As QueryDef
Dim frm As Form
Dim i As Integer
Dim strName As String
Dim strName2 As String

'Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frmVoucherReports

'Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryVouchRecvd2")

'Set parameters for query based on values entered
'in Vouchering Reports Form form.
qdf.Parameters("Forms!frmVoucherReports!cboProgramType") _
= frm!cboProgramType
qdf.Parameters("Forms!frmVoucherReports!cboYear") _
= frm!cboYear

'Open Recordset object.
Set rstReport = qdf.OpenRecordset()

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

For i = 1 To intColumnCount
strName = rstReport.Fields(i - 1).Name
strName2 = Mid(strName, 3, Len(strName))
Me.Controls("lblHead" & i).Caption = strName2
Me.Controls("Col" & i).ControlSource = strName2
Next i

Thanks for your help

 

[dhookom]

I don't care much for the crosstab solution you are using. I prefer the crosstab solution found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If your columns are date intervals, there are much better solutions.

If you can't figure this out, come back with the SQL view of your crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EMoore12]

I downloaded your sample report and read over the instructions but I still had trouble understanding how to make it work. My headings are not dates. The headings are State Qtr 1, Qtr 2, Qtr3, Qtr 4, Payment 1, Payment 2, etc. The query sorts the headings alphabetically and I want the headings to appear on the report just the way I've typed them above. I'm posting the SQL view of my query.


Thanks.

PARAMETERS [forms]![frmVoucherReports]![cboProgramType] Text ( 255 ), [forms]![frmVoucherReports]![cboYear] Text ( 255 );
TRANSFORM First(qryVouchRecvd1a.Processed_Date) AS FirstOfProcessed_Date
SELECT qryVouchRecvd1a.[01State], qryVouchRecvd1a.[02UserName] AS [Project Officer]
FROM qryVouchRecvd1a
WHERE (((qryVouchRecvd1a.Quarter1)<>""))
GROUP BY qryVouchRecvd1a.[01State], qryVouchRecvd1a.[02UserName]
PIVOT qryVouchRecvd1a.Quarter1;

 

[dhookom]

Are you suggesting your Quarter1 field contains values like:
State Qtr 1, Qtr 2, Qtr3, Qtr 4, Payment 1, Payment 2, etc?
Do you have a value stored in a table that describes the sorting? Can you use the Column Headings property of your crosstab query?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]