asked on

Not valid field error when using crosstab query as record source for report

I created a report using a crosstab query as the source. Everything works fine as long as the criteria the user selects includes data for all the columns. What do you do if it doesn't? How do I get around the error and just leave that column blank?
Thanks in advance.

ASKER CERTIFIED SOLUTION

peter57r

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Mike Eghtebas

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

BobRosas

ASKER

Thank you both for your quick responses. I'm trying to assign column headings to see if I can get that to work but I can't even get the assigned names to work. If I run CodeA below I get all the data I expected(the 6 columns have data for each column). If I run CodeB, the headings show but I don't have any data under any of the 6 headings. Any thoughts!
Thanks so much!

CodeA
TRANSFORM Count(tblMTRptAge.AgeCount) AS CountOfAgeCount
SELECT tblMTRptAge.County, tblMTRptAge.ServiceLkUpID, Sum(tblMTRptAge.Age) AS SumOfAge
FROM tblMTRptAge
GROUP BY tblMTRptAge.County, tblMTRptAge.ServiceLkUpID
PIVOT tblMTRptAge.AgeGrp;

CodeB
TRANSFORM Count(tblMTRptAge.AgeCount) AS CountOfAgeCount
SELECT tblMTRptAge.County, tblMTRptAge.ServiceLkUpID, Sum(tblMTRptAge.Age) AS SumOfAge
FROM tblMTRptAge
GROUP BY tblMTRptAge.County, tblMTRptAge.ServiceLkUpID
PIVOT tblMTRptAge.AgeGrp In ("To16","To24","To34","To44","To54","Over54");

BobRosas

ASKER

Disregard above. I thought I could name the columns anything I wanted but as soon as I named the columns to match the field names the data showed. The report seems to be working now. I couldn't have done it without you guys. Thanks you both for your help.