From cross tab query to report: Error Microsoft jet database

[ramab]

Hi friends,
Im getting an error [The microsoft jet database engine does not recognize" " as valid field name or expression] when running my report which is related to a crosstab.my crosstab query will display null but when i run the report some of the fields are blanks.
e.g In my cross tab query:
Completed: 2
InProgress: 3
Pending:
But when i run the report it does not recognize the field pending and give the error i mentioned above.
Any suggestions???Thx
Rama

[puppydogbuddy]

Hi friends,
Im getting an error [The microsoft jet database engine does not recognize" " as valid field name or expression] when running my report which is related to a crosstab.my crosstab query will display null but when i run the report some of the fields are blanks.
e.g In my cross tab query:
Completed: 2
InProgress: 3
Pending:
But when i run the report it does not recognize the field pending and give the error i mentioned above.
Any suggestions???Thx
Rama

Rama,

Try wrapping Pending with the Null To Zero function:
Nz([Pending],0)

[ramab]

Rama,

Try wrapping Pending with the Null To Zero function:
Nz([Pending],0)

Hi,the thing is that "pending" is the field generated when i run the report. if i previously run the report where the heading pending header is generated,then when i run my report again but that there is no pending field,it will give me Microsoft jet database engine error.
Thx

[puppydogbuddy]

Hi,the thing is that "pending" is the field generated when i run the report. if i previously run the report where the heading pending header is generated,then when i run my report again but that there is no pending field,it will give me Microsoft jet database engine error.
Thx

You need to provide more details.
1. How do Completed, In Progress, and Pending appear in your crosstab query grid? are they column names or are they values appearing under a column like Status?
2. The best place to fix these types of problems is in your query. You've indicated that you are getting nulls......If "completed", "in progress", etc are values appearing in a column of your query, then all you need to do is enter the following in the criteria row of the column that returns the nulls >>>>Is Not Null
3. If In Progress, etc. is a field/column name in your crosstab, then you can eliminate the problem with nulls by "fixing" your column names in the pivot clause in sql view like this :

Pivot XXXXXXXXXXXXXX In ("Completed","In Progress","Pending");

[ramab]

Hi, I hope the detail below is ok. Thx

The crosstab query

The SQL
TRANSFORM Count(CORRECTIVE_ACTIONS_V6_1.FOL_STAT_CODE) AS CountOfFOL_STAT_CODE
SELECT CORRECTIVE_ACTIONS_V6_1.DEPT_NAME, Count(CORRECTIVE_ACTIONS_V6_1.FOL_STAT_CODE) AS [Total Of FOL_STAT_CODE]
FROM CORRECTIVE_ACTIONS_V6_1
GROUP BY CORRECTIVE_ACTIONS_V6_1.DEPT_NAME
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC;

The datasheet

Total Of FOL_STAT_CODE Completed In Progress

DepartmentName1 5 5
DepartmentName2 4 2 2
DepartmentName3 3 1 2


[The fol_stat_code that generates the field completed, in progress. I have a form where I specify which department I want; if I select all departments then I have no problem but if I select departmentname1 then as the field In Progress is empty then I get the problem jet data base, this is because (Im not sure) that the field in progress was created previously. ]

I then use this cross tab query to build the report.

[puppydogbuddy]

Hi, I hope the detail below is ok. Thx

The crosstab query

The SQL
TRANSFORM Count(CORRECTIVE_ACTIONS_V6_1.FOL_STAT_CODE) AS CountOfFOL_STAT_CODE
SELECT CORRECTIVE_ACTIONS_V6_1.DEPT_NAME, Count(CORRECTIVE_ACTIONS_V6_1.FOL_STAT_CODE) AS [Total Of FOL_STAT_CODE]
FROM CORRECTIVE_ACTIONS_V6_1
GROUP BY CORRECTIVE_ACTIONS_V6_1.DEPT_NAME
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC;

The datasheet

Total Of FOL_STAT_CODE Completed In Progress

DepartmentName1 5 5
DepartmentName2 4 2 2
DepartmentName3 3 1 2


[The fol_stat_code that generates the field completed, in progress. I have a form where I specify which department I want; if I select all departments then I have no problem but if I select departmentname1 then as the field In Progress is empty then I get the problem jet data base, this is because (Im not sure) that the field in progress was created previously. ]

I then use this cross tab query to build the report.

Try changing this:
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC;

To This:
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC IN ("COMPLETED","IN PROGRESS", "PENDING") ;

[ramab]

Try changing this:
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC;

To This:
PIVOT CORRECTIVE_ACTIONS_V6_1.STAT_DESC IN ("COMPLETED","IN PROGRESS", "PENDING") ;

Thx a lot. It work;-)