Report chart code: ISNULL or ISImpossible?

[EdaxFlamma]

Hey all,



I have several charts on a report that are populated using code similar to that which is found below.

SELECT (Format([ShipDate],"WW" )),Sum([AmtUnits]) AS [SumOfAmtUnits] FROM [q_MonthlyShip]   
GROUP BY (Year([ShipDate])*CLng(54) + DatePart("ww",[ShipDate],0)-1),(Format([ShipDate],"WW" ));

When I have sales data for each week, all the charts populate normally. If I fail to have sales data for that week, the entire week is omitted from my graph. See attached picture below (pardon the scale issue):


My goal is to have the Product2 x-axis mirror the Product 1 graph despite the fact that there are no sales for that product for weeks 21,22,25,26,28,29.

My first thought was to try and shove an ISNULL [AmtUnits],0 in there to set the number of units sold per week to 0 but I can't seem to get it to work. After thinking about it however, is what I am trying to do even possible given that Access doesn't "know" that those weeks exist without [ShipDate]s to go off of?

If I can provide more information please let me know.

Thanks a bunch!

[June7]

Maybe. What chart type are you using? A Scatter type might get you what you want. All of my graphs are Scatter. This allows me to set scale of x and y axes. The data has to be numeric. The Format function results in a string, even if it contains only digits, might still be treated as a string by the graph engine. DatePart() results in a number value. Or use Val() on the Format() result.

I analyse graph issues best with data. If you want to provide, follow instructions at bottom of my post.

[EdaxFlamma]

Sorry it took so long for me to get back to you. Had to cull a lot of data...

Be forewarned that this is only a partial database and that it does contain many examples of bad practice. I'm trying to focus on salvaging what I can from this database and build a new one in my "spare time".

If I can be of any more assistance or clarify anything please let me know.



NoSalesCharts.zip

[June7]

I opened the report and Product1, Product2, Product3 graphs show X-axis with no gaps - Product4, Product5, Product6 do show gaps.

The report opened without error to ReportView but when sometimes when I switch to DesignView and always direct PrintPreview, get:
The record souce "SELECT (Format([ShipDate],"WW")),..." specified on this form or report does not exist.
Followed by:
An error occrred while sending data to the OLE server (the application used to create the object).
Then Product2 graph won't display in PrintPreview.
AhHah - Product2 is misspelled as Prodcut2 in the RowSource SQL.

I am afraid the only way to get what you want is to make sure there is at least one record for each week. Scatter chart will show a scale without gaps but it will start at 0 unless you manually change it or use VBA to automate. Also, it won't show a continuous line because the data still has gaps.

[EdaxFlamma]

I wondered about that (isimpossible). I'll check out the scatter plot and see if that works better for us. Many thanks and my apologies for the typo... Dyslexics of the world untie!

I think we are solved but I'll let you close us out if you have anything else to add. Much appreciated again!