Null Values in Crosstab query Report Headers 1

[Bjornson15]

Hello everyone,

I am trying to put together an OnOrder report. In my table I have Lot/Week Ending Date/Quantity as my columns. Because of the way I have the data, I need to use a crosstab query to extract each individual Week Ending Date across the top of the report.

I have given the column headers in the crosstab query names like "Wk 0", "Wk 1".... "Wk 5". I only need to see 5 weeks out. I also have Criteria given to this query from my form. All of this is working perfectly.

The problem that I am coming across is when I filter and there is only 1 or 2 columns left (leaving the others without the column name in the query). This doesn't happen when I run the report when every column has info. I can run the query but I get an error when I try to run the report.

"The Microsoft Jet database engine does not recognize 'qryOO_WEEK'(the query name) as a valid field name or expression."

If you need a better explaination of my questions, ask questions... and I will get back to you. If you know what the problem is... please help me out.

Bjornson

 

[dhookom]

How about sharing your SQL view of the crosstab query? Did you enter values into the Column Headings property?

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]

 

[Bjornson15]

Here is the SQL:

PARAMETERS [forms]![frmONORDER]![CAT_SEL] Short, [forms]![frmONORDER]![DEPT_SEL] Short, [forms]![frmONORDER]![CLS_SEL] Short;
TRANSFORM Sum(ONORDER.TOTAL) AS SumOfTOTAL
SELECT ONORDER.NUM_LT, LOT_MASTER.DES_LT_NUM, LOT_MASTER.CD_SYL, LOT_MASTER.CD_NAR_ALI_SSN_YR, LOT_MASTER.CD_CRN_ALG_CTG, LOT_MASTER.DES_CRN_ALG_CTG, LOT_MASTER.CD_CRN_ALG_DPT, LOT_MASTER.DES_CRN_ALG_DPT, LOT_MASTER.CD_CRN_ALG_CSS, LOT_MASTER.DES_CRN_ALG_CSS, LOT_MASTER.CD_CRN_ALG_SCL, LOT_MASTER.DES_CRN_ALG_SCL, Sum(ONORDER.CASES) AS CASES, Sum(ONORDER.SOLIDS) AS SOLIDS
FROM PSS_DATE LEFT JOIN (LOT_MASTER RIGHT JOIN ONORDER ON LOT_MASTER.NUM_LT = ONORDER.NUM_LT) ON PSS_DATE.DATE = ONORDER.ETA
WHERE (((PSS_DATE.DATE) Between Now()-21 And Now()+49) AND ((LOT_MASTER.CD_CRN_ALG_CTG)=IIf([forms]![frmONORDER]![CAT_SEL] Is Null,[CD_CRN_ALG_CTG],[forms]![frmONORDER]![CAT_SEL])) AND ((LOT_MASTER.CD_CRN_ALG_DPT)=IIf([forms]![frmONORDER]![DEPT_SEL] Is Null,[CD_CRN_ALG_DPT],[forms]![frmONORDER]![DEPT_SEL])) AND ((LOT_MASTER.CD_CRN_ALG_CSS)=IIf([forms]![frmONORDER]![CLS_SEL] Is Null,[CD_CRN_ALG_CSS],[forms]![frmONORDER]![CLS_SEL])))
GROUP BY ONORDER.NUM_LT, LOT_MASTER.DES_LT_NUM, LOT_MASTER.CD_SYL, LOT_MASTER.CD_NAR_ALI_SSN_YR, LOT_MASTER.CD_CRN_ALG_CTG, LOT_MASTER.DES_CRN_ALG_CTG, LOT_MASTER.CD_CRN_ALG_DPT, LOT_MASTER.DES_CRN_ALG_DPT, LOT_MASTER.CD_CRN_ALG_CSS, LOT_MASTER.DES_CRN_ALG_CSS, LOT_MASTER.CD_CRN_ALG_SCL, LOT_MASTER.DES_CRN_ALG_SCL
PIVOT IIf(DateDiff("ww",Date(),[ETA])<0,DateDiff("ww",Date(),[ETA])+52,DateDiff("ww",Date(),[ETA]));


The report is 1 column in the column headings properties. The information that comes out of the query looks something like this: (the number is the number of weeks away from today)


Lot 0 1 2 3 4
1 100 100 100 100
2 100 100 100 100
3 100 100 100 100

The problem occurs when I would filter to only show Lot 1. The query result would then be:

Lot 0 1 3 4
1 100 100 100 100

There is no column number 2. The report looks for data in column 2. I just want it to show either null or 0. But I need to still show column 2.

Hope this clarifies my question. Thanks for your help.

 

[dhookom]

Did you enter values into the Column Headings property?

Crosstab queries have a Column Headings property where you can type in all the possible column heading values. You would need to enter:
Column Headings: 0,1,2,3,4
I don't care for numeric column names and will generally concatenate "W" or "WK" to derive columns of WK0, WK1,...

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]

 

[Bjornson15]

Worked like a charm! Thanks, your help is greatly appreciated!

About the numerical column headings, I figured that as long as the system knows what it is looking at, I don't need to put some more arbitrary characters in front of it. I might do that so the next person that looks at the file knows what it is counting though.

Thanks for your advise and help!