Access Chart Empty Points

In an Access form with a chart I have time (weeks) along the x axis (bottom) and cumulative values expressed as lines.

Some fields have values to the end of the time line (eg Budget) others show vlaues up until the current time (eg expenditure) and should stop where the values become null.

Currently these lines descend to zero instead of terminating where the values become null.

There should be somewhere I can set teh line properties for empty points to become invisible.

Where is it?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
are you sure the value is null and not zero ?
kentgorrellAuthor Commented:
kentgorrellAuthor Commented:
Not sure what I've done now but the second line is adding to the first!
Sitll does not terminate on Null
an sccdb with table and form is attached.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
First, I will say that you will find much more support for this if you used a standard chart, not a pivot chart.

<Not sure what I've done now but the second line is adding to the first!>
Not sure what this means or how it applies here.

Please post a graphical example of what you have now, then post another graphic example of *exactly* what you want.

I will say that this is just the way line charts work by default.
So try using a bar/column chart instead.
kentgorrellAuthor Commented:
Did you look at the sample accdb that I posted?

If so then you can see a graphical example of what I have now.

•It clearly shows that the two values are added to give the second line which should just be the Purchase_Order_Total.

•If you remove the Projected_Budget from graph then the Purchase_Order_Total value is correct but the line continues down to zero when it gets to a null value.

Here is the accdb again, in an even more simple form, with just that line.It contains just one table with 4 records.

Jeffrey CoachmanMIS LiasonCommented:
Again, this is the way a line chart was designed to display data.

Your "series" spans 4 record, ...hence 4 "points" will always be plotted.
So the "Line" will continue through (and "connect") the entire series.
Make sense?

So unless you are willing to invest the time to:
1. Figure out how to limit the series based in the number of ending Zero or null values
2. Figure out how to do this in a pivot table
3. Figure out how to dynamically adjust the code for different series' ending with null/zeros at different points
...this may be difficult.

This is why I suggested a bar or column chart.  With no "Line" needed to connect the points, the "cutoff" will be cleaner.

See the modified sample attached.
Jeffrey CoachmanMIS LiasonCommented:
kentgorrellAuthor Commented:
Easy solution for anyone with a bit of experience.
I was using the wrong type of line graph.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kentgorrellAuthor Commented:
The solutions offered were not in the least bit helpful. Although capricorn1's suggestion to make sure the value was null was at least on topic.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.