Why can't I get passes this error?

[David_Skov]

I create a crosstab table that includes a quarter for 2003 and 2004 respectivly. But since we are in the middle of a quarter right now November and december data is still missing.

In my crosstab I have the following variables

LYKV1 (Last year 1. month in the quarter)
LYKV2 (Last year 2. month in the quarter)
LYKV3 (Last year 3. month in the quarter)
TYKV1 (This year 1. month in the quarter)
TYKV2 (This year 2. month in the quarter)
TYKV3 (This year 3. month in the quarter)

But since november and december data are missing for 2004, TYKV2 and TYKV3 is unavailable. Therefor I would need my report to enter zeros (0) for each output.

I have tried this formula:

=IIf(IsError([TYKV2]);0;[TYKV2])

But it returns 0 even though the variable TYKV2 has a value (I can give it a value).

I also tried to use the Nz function but with no luck. It return '#error'

What should I do to correct this formula?

In advance tx

 

[David_Skov]

Perhaps I explain myself badly (Don't hesitate to tell me that)

I simply need a way to filter out a variable in a report if the variable isn't present in the query as it's the case in my problem. So for 3. quarter I have these periods:

LYKV1 (July 2003)
LYKV2 (August 2003)
LYKV3 (September 2003)
TYKV1 (July 2004)
TYKV2 (August 2004)
TYKV3 (September 2004)

Now that we are in the 4. quarter of 2004 I have these periods:

LYKV1 (October 2003)
LYKV2 (November 2003)
LYKV3 (December 2003)
TYKV1 (October 2004)
TYKV2 (missing)
TYKV3 (missing)

TYKV2 and TYKV3 is missing since data ain't three yet. I think that the access report see this as a missing variable so how can I tell Access to just paste zeros (0) were data is missing?

 

[David_Skov]

Hmmm seems to a lot harder to crack than I thought

 

[Philem]

Hullo! This is rather easy to do. When you are in Design View of the query, go to the Properties of the query, and in the Column Headings section, type:

LYKV1,LYKV2,LYKV3,TYKV1,TYKV2,TYKV3

This will make sure that TYKV3 shows in the query. When a crosstab query runs, and there is no information for something that it would normally make a column for, it just doesn't show that column. This way, you can force columns.

HTH
P

 

[David_Skov]

TX!...That worked

 

[David_Skov]

Now I just need to either fill in the empty cells with 0's (Zero's) in my query

Or make my report show 0 (Zero) if the cell has no value/is empty

How to?

 

[mdmilner]

Check out the Nz function

Mike