Need a little help to solve this problem with my report

[Bokazoit]

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.

What should I do to correct this formula?

In advance tx

 

[Crowley16]

eh, not sure if this will work or not, but have you tried:

=NZ(tykv2, 0)

--------------------
Procrastinate Now!

 

[Bokazoit]

It gives me #error ?

 

[Bokazoit]

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?

 

[dhookom]

You can set the Column Headings property of your crosstab. A better method (IMHO) is to use relative date intervals as described in faq703-5466. The FAQ uses months but this could easily be modified for quarters or whatever.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Bokazoit]

@dhookom: You said: 'You can set the Column Headings property of your crosstab'

What You mean?

Well they are dynamic since they aren't depended on the actual month. They change depending on the quarter I look at. The only problem is that when looking at this quarter we have some unfinished months and there for the crosstab contains no data for these month.

I would just like my report to ignore the missing months and insert a 0 (zero) instead

 

[dhookom]

The solution to dynamic columns is in the faq that I provided. Did you read it?

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Bokazoit]

Yes I read it, but plz read my threads again then. My variables are dynamic. Each of the 6 variables can take the value of 3 months for 2003 and 2004 respectivily depending on the quarter I choose.

In contrary to the dynamic report as described in Your link, the current month is Mth0, last month Mth1 etc. In my situation, I have 2 months (TYKV2=november & TYKV3=december) that hasn't any data yet, simply because there is no data available. Therefor I need TYKV2 & 3 to be 0 (zeros) now. When we enter december, only TYKV3 needs to be zero.

I don't know any real good way to do this which is why I ask in here.

 

[dhookom]

I still think the solution is in the FAQ. Perhaps you should share your SQL.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Bokazoit]

If You think that, then how?

Here is the SQL from my Crosstab-table which I build my report on:

TRANSFORM Sum(Dataopsamlingstabel.Omsætning) AS SumOfOmsætning
SELECT Dataopsamlingstabel.Kæde, Dataopsamlingstabel.[Kæde-Sub], Dataopsamlingstabel.Butiksnavn, Dataopsamlingstabel.[CP kategori]
FROM Dataopsamlingstabel
GROUP BY Dataopsamlingstabel.Kæde, Dataopsamlingstabel.[Kæde-Sub], Dataopsamlingstabel.Butiksnavn, Dataopsamlingstabel.[CP kategori]
PIVOT Dataopsamlingstabel.Periode;

PIVOT Dataopsamlingstabel.Periode; <-- This is the part that creates the monthly data for whatever quarter I choose

 

[dhookom]

It looks like you are storing a value that represents a date but isn't a date type. I guess I had assumed there was a date someplace in your table. Since you don't have a date, you can easily convert your Periods field by creating a lookup table with records like
LYKV1 7/1/2003
LYKV2 8/1/2003
LYKV3 9/1/2003
TYKV1 7/1/2004
TYKV2 8/1/2004
TYKV3 9/1/2004
....
You can then associate your values with a date and use my solution.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]