Ignore empty record

[honeypot3]

Hi there

I have a report which produces a chart from the SQL below:

SELECT NewOutputSchemeOverallCombi.Trade, NewOutputSchemeOverallCombi.Percent AS [Overall %age], NewOutputSchemeOverallCombi.Site AS [Site %age]
FROM NewOutputSchemeOverallCombi;

This feeds off a query which produces blank entries in the Contract number field where site percentage values are zero as ot all sites cover all criteria:

SELECT [Overall trade avgs].Month, [Overall trade avgs].Year, [Overall trade avgs].Trade, [Scheme averages].[Contract number], [Overall trade avgs].Percent, Val(Nz([Percentage Average],0)) AS Site
FROM [Overall trade avgs] LEFT JOIN [Scheme averages] ON [Overall trade avgs].Trade = [Scheme averages].Trade
WHERE ((([Overall trade avgs].Month)="Jan") AND (([Overall trade avgs].Year)="2005"));

This works fine until I want to be able to use the underlying query to provide a contract number label for the chart. I have placed the chart inside the contract number header area hoping this would produce only one chart dependent on the contract number that the user specifies. Unfortunately the report is also producing a chart to represent the blank entries as well. Is there some code I can place under the contract number header which will get the report to ignore the blank contract number?

 

[hneal98]

You can try something like this...

SELECT [Overall trade avgs].Month, [Overall trade avgs].Year, [Overall trade avgs].Trade, [Scheme averages].[Contract number], [Overall trade avgs].Percent, Val(Nz([Percentage Average],0)) AS Site
FROM [Overall trade avgs] LEFT JOIN [Scheme averages] ON [Overall trade avgs].Trade = [Scheme averages].Trade
WHERE ((([Overall trade avgs].Month)="Jan") AND (([Overall trade avgs].Year)="2005")) and len([Scheme averages].[Contract number]) > 0

UNION

SELECT [Overall trade avgs].Month, [Overall trade avgs].Year, [Overall trade avgs].Trade, "99999999", [Overall trade avgs].Percent, Val(Nz([Percentage Average],0)) AS Site
FROM [Overall trade avgs] LEFT JOIN [Scheme averages] ON [Overall trade avgs].Trade = [Scheme averages].Trade
WHERE ((([Overall trade avgs].Month)="Jan") AND (([Overall trade avgs].Year)="2005")) AND len([Scheme averages].[Contract number]) < 1;

 

[honeypot3]

Thanks I will give it a try.