Unrecognized Expression

[DrStephen]

OK, here's the problem (and I think 'help' is descriptive)...

I'm running a report that counts incidents by program (e.g., 'Program A', 'Program B'...). The report is based upon a crosstab query that summarizes incident type and subtype by program. The report needs to present all programs -- even if the incident count = 0). The crosstab output does not show a program if the count = 0. So, if Program B had no incidents, there is no 'Program B' in the CT output. Now, because I have formulae in the report that summarizes program and total counts, I'm getting an error message

"The Microsoft Jet Database Engine does not recognize "[Acute B (2W)]" as a valid field name or expression"

By the way "[Acute B (2W)]" is the actual name of Program B that is being used.

I had this problem come up before and came up with a fix (meaning that someone from this forum figured it out). I think I recall that I had to do something with the crosstab query to make it generate a (in this case) column for every program, even if all the cells were empty.

I also recall that it was a simple fix. Anyone have any ideas?

NB: Part of the problem may be a conversion from Access 2000 to 2003. This report used to run correctly.

 

[lupins46]

If it's a short list then you can just type it into the column headings property of the query.

"program1";"program2"; etc.

The values must be exactly as found in the data.

 

[dhookom]

Any time you have a crosstab question, you should provide your SQL view.

I expect you should have a table with every different Program. Join to this table with a join that displays every record from the table and then use a field from this table as the column heading.

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]

 

[DrStephen]

...that's it...column heading properties...although I like the join approach...

Thanks, guys...