Report error when quering via year

[yoritomo]

Hi, I should point out this is my first time creating a report

I have a report that runs a query that gets data based on the entered year, it works, until you check 2005
Once you do however the report throws up an error because it cant find any data for months 11 and 12 for 2005 and says "microsoft jet database doesnt recognise " as a valid field name or expression
I was wondering is there any way to stop this from happening? so that it would just return a blank in those fields

Also, I was wondering if anyone could tell me how to put the totals of the values in another field, then the average in another

the data looks like this

code jan feb mar etc
tfrew 34 789
tyesr 567 45

I want it to get the total over the months, and to get the average of only the months with data, Ie if code tfrew didnt sell any in june, I dont want it to count june in the average

 

[ScottGem]

You left out a key piece of into here. It would appear you are working with a crosstab query as the basis of the report. If that's the case, what you need to do is use a Left join that will add blank records for each month.

 

[yoritomo]

Ah yes I am indeed, erm, how do I left join it? its just querying the data from a single table
Hmm, ok I made a left join on it anyway, by hooking in the main item table and querying using the item number, but it still throws up the same error, because the report is asking for dates for the 11th and 12th month of 2005, its not the fact there is no data for it, its seems to be those 2 months, because they dont exist yet, anyone know how to get around this?

 

[ScottGem]

You need a table of months or dates that you can join to. You then use the number 2 join of all records from this other table and matching records from your main table.

 

[yoritomo]

Still throwing up the same error
Even with another month table connecting to the query with a join connecting the two of them saying to join using all of month, and only the ones in sales history that match its still throwing the error up I'll put my sql code down just incase

PARAMETERS [Year] Short, [Stock Code] Text ( 255 );
TRANSFORM Sum([Sales History].VALUE_AT_BUYING_PRICE) AS SumOfVALUE_AT_BUYING_PRICE
SELECT [Sales History].ITEM_NUMBER
FROM [Month] LEFT JOIN [Sales History] ON Month.Month = [Sales History].Month
WHERE ((([Sales History].ITEM_NUMBER) Like [Stock Code] & "*") AND (([Year])=Year([TRANSACTION_DATE])))
GROUP BY [Sales History].ITEM_NUMBER, [Year]
ORDER BY [Sales History].ITEM_NUMBER
PIVOT Month.Month;

 

[yoritomo]

What i was wondering though is is there any code so that if there is no value for the field, ie the query still only outputs up to month 10, so there being no 11 or 12 that it would just display a blank field instead of crashing the report, or if push comes to shove can anyone give me any advice on using vba to solve the problem

 

[Rich]

TRANSFORM Format(Nz(Sum([SumOfDebits]),0),"Currency") AS TotalDebits SELECT YearlyQry.FYear AS FiscYear FROM YearlyQry GROUP BY YearlyQry.FYear PIVOT YearlyQry.Heading;

 

[yoritomo]

*watches the plane fly over his head*
Sorry, I erm, dont really get it, Would this give me the same output as a crosstab?

 

[Rich]

Try using the Nz function as posted in the example above

 

[yoritomo]

I already have an nz in each of the report fields (for each month) it is like this

=IIf(nz([10],0)=0,0,[10])

with the 10 being the month number, so this would be for october, is this using it correctly? I think I got this bit of code from another post on this forum somewhere, although chances are it could have gone wrong when I modified it

 

[Rich]

Nz Null to Zero will only transform records with nulls to Zero. It will not turn No records into Zero values

 

[Rich]

Looking at it again, try using the Column headings property in the underlying query

 

[yoritomo]

so to change that into my code it would be

PARAMETERS [Year] Short, [Stock Code] Text ( 255 );
TRANSFORM Sum([Sales History].VALUE_AT_BUYING_PRICE) AS SumOfVALUE_AT_BUYING_PRICE 
SELECT [Sales History].ITEM_NUMBER
FROM [Month] LEFT JOIN [Sales History] ON Month.Month = (Nz(Sum([Sales History].Month),0),"Month")
WHERE ((([Sales History].ITEM_NUMBER) Like [Stock Code] & "*") AND (([Year])=Year([TRANSACTION_DATE])))
GROUP BY [Sales History].ITEM_NUMBER, [Year]
ORDER BY [Sales History].ITEM_NUMBER
PIVOT Month.Month;

?
I cant say I'm very sure where I should put it in the code

 

[yoritomo]

Yeah my column heading is set to Month.Month which is set up to be a table with numbers just going 1-12 only when it hits 2005 it cuts it down to 10 even with a join property telling it to display all numbers from 1-12 regardless of what is in the sales history file

 

[ScottGem]

You need to do the join in an interim query BEFORE the corsstab.

 

[yoritomo]

oh right, I just did it through the normal access drop down menus, I actually havent touched my sql, thanks I'll give that a shot though

Oh wait I think I get what you mean, have a query run before hand then run the crosstab from that?

 

[ScottGem]

Exactly, The interim query will then have a record for each month. so the Crosstab will pick it up.

 

[yoritomo]

Blast it, no love there, It didnt create the records, I dont really know why, it should, but its not
The preCrosstab Queries sql is

SELECT [Sales History].ITEM_NUMBER, Month.Month, Year([TRANSACTION_DATE]) AS [Year], [Sales History].VALUE_AT_BUYING_PRICE
FROM [Month] LEFT JOIN [Sales History] ON Month.Month = [Sales History].Month;

And the cross tab is now

PARAMETERS [Year] Short, [Stock Code] Text ( 255 );
TRANSFORM Sum([Pre-CodeQuery].VALUE_AT_BUYING_PRICE) AS SumOfVALUE_AT_BUYING_PRICE
SELECT [Pre-CodeQuery].ITEM_NUMBER
FROM [Pre-CodeQuery]
WHERE ((([Pre-CodeQuery].ITEM_NUMBER) Like [Stock Code] & "*") AND (([Pre-CodeQuery].Year)=[Year]))
GROUP BY [Pre-CodeQuery].ITEM_NUMBER
PIVOT [Pre-CodeQuery].Month;

 

[ScottGem]

can you post a stripped down copy of your database?

 

[yoritomo]

Can do, I'll post it once I strip it down