X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Create A Date Range For A Chart On A Report, Access 2016    
post Nov 6 2019, 04:02 PM

Posts: 1,175
Joined: 1-December 12

I have a chart on a report that is based on query. The query has a field named TDate. I'm trying to limit the chart's range by creating a date range for this field. No matter what i do the chart seems to display all the data in the query ignoring the range(s) I've created.
I have a good understanding of date ranges and use them all the time. Charts on the other hand i usually do in Excel. I' like to start using charts in ACCESS more,as it seems like they have been improving.

Go to the top of the page
post Nov 6 2019, 08:13 PM

UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA

Hi: I suppose we should start by looking at the query, with your date criteria. Can you post that for us?

Even better, if you could, post a cutdown db with relevant objects and test data, along with a description of specifically how you want to limit the data. then someone could give you a better response.


"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
post Nov 6 2019, 08:16 PM

Posts: 37
Joined: 20-October 11
From: Brisbane

I use the code similar to the following called from the Report_Open event:

With Me!SalesGraph
.RowSource = "SELECT DISTINCTROW sDate, SUM(FldName) AS Sales FROM QueryName WHERE sDate BETWEEN #" & Me!BeginDate & "# AND #" & Me!EndDate & "# GROUP BY sDate"

Hope this helps.

Go to the top of the page
post Nov 21 2019, 09:15 AM

Posts: 262
Joined: 27-September 01

You could use the debugger or a msgbox() to see what the value of .RowSource turns out to be. You could substitute fixed dates for Me!BeginDate and Me!EndDate to see what happens. What exactly are Me!BeginDate and Me!EndDate? What are they bound to? Maybe they don't have values yet during the .Open event, or maybe they don't after the .Requery.
Go to the top of the page
post Nov 21 2019, 09:27 AM

Posts: 262
Joined: 27-September 01

Another thought: do you really want to hard-code your start-and-end-date filter into the report? If users are entering start and end dates in textboxes on a form, and maybe clicking a Run Report button, you can build any filters you like and then pass them in the WHERE condition parameter of DoCmd.OpenReport. Also, if a user wants to run a report from, say, 3/31/2019 to the present, the user can leave the End Date blank, and you can build a filter condition without an end date, such as sDATE >= #3/31/2019# rather than always using BETWEEN and always needing both a start and an end date.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    9th December 2019 - 11:29 PM