CODEWITHSUNDEEP
sqlms-accesssyntax-errorcrosstab
TMY
TMY

Reputation: 469

Access '13 - Does not recognize field name Error, Crosstab to Report Issue

The Background

Each month I run a crosstab query which is then "spruced up" with an Access Report.

CROSSTAB SQL:

TRANSFORM Sum(myTable.Field1) AS SumOfField1
SELECT myTable.Date, Sum(myTable.Field1) AS [Daily Totals]
FROM myTable
GROUP BY myTable.Date
PIVOT myTable.Field2;

where Field1 is a $ Amount, and Field2 (for this example) is either going to be "Option1", "Option2", or "Option3".

TYPICAL CROSSTAB RESULTS:

Date     Option1    Option2   Option3
-----    --------   --------  --------
Day1      $5.00      -$2.37   
Day2                  $3.15
Day3                           $2.22

My Issue

Because 99 times out of 100 I'm going to have data in each "option" in a given month I created a report that cleans up the crosstab results in a "pretty" format. This month however, my raw data didn't have any "Option3" values. As a result the crosstab column for Option3 doesn't appear on my query results. Then, since the report pulls directly from the crosstab & looks for each option column by name, this causes my report break giving the error:

The Microsoft Access database engine does not recognize '[Option3]' as a valid field name or expression.

Troubleshooting

I've tried a few things with respect to troubleshooting:

  1. =Nz(Sum(myTable.Field1),0) in the SQL... however this just yields 0's where NULL for the existing values of field2 - thus "Option3" does not appear as a column of all 0's.
  2. =Nz([Option3],0) in the report... still get the error above (field not recognized)
  3. =IIf(IsError(Option3),0,(Option3)) in the report... get the error:

The expression you entered contains invalid syntax

  1. =IIf(IsEmpty(Option3),0,(Option3)) in report... same as above
  2. =IIf(IsMissing(Option3),0,(Option3)) in report... same as above

At this point, I'm at a loss regarding how to get my existing report to run (just displaying a column of 0's for "Option3").

Upvotes: 1

Views: 2162

Answers (1)

HansUp
HansUp

Reputation: 97101

Specify column headings with an IN list in the PIVOT clause.

PIVOT myTable.Field2 IN ('Option1', 'Option2', 'Option3');

Those column headings will then be included in the query result set (in the listed order) regardless of whether or not the source data includes any rows with those values.

A side effect is if the crosstab data source could ever include an Option4, it would not be included in the resulting columns. That is fine for your report since it wasn't designed to expect an Option4 column. But it might be a concern if you're using the crosstab query elsewhere.

In the report, you can use Nz() to substitute zero for Nulls from the options columns.

Upvotes: 4

Related Questions