Graph the data from multiple fields

[Sonny101]

So wait it that ControlSource for the textbox? and is the [enter start] and [enter end] supposed to be [[Forms]![Par-Pak final].[startdate]] and [[Forms]![Par-Pak final].[enddate]] ? For the form part i triend changing the Graph RowSource to





SELECT Union3T1.Act, Sum(Hrs) FROM Union3T1 WHERE [Date] BETWEEN [StartDate] AND [EndDate] GROUP BY Union3T1.Act;

with the two comboboxes (with names Start Date and End Date) in the form to be SELECT Table1.Date FROM Table1 GROUP BY Table1.Date ORDER BY Table1.Date DESC;

but that only produced an empty report

[June7]

I am so lost and confused. Why has this become so complicated? Do you want to use popups or reference to form controls as parameter inputs? The SQL example in my last post was for a report RecordSource using popup inputs. Yes, the popups could instead be references to form controls.

Does the UNION query have a field named Date (bad idea to use reserved word as field name)?

[Sonny101]

Lol yeah we got caught up between trying to solve a problems using two different ways at the same time Just to be clear, all my reports are currently set up as using pop ups as the parameter input, so I just wanted on each of the graph I created, to display the start date and end date to know graph of what timeperiod we are looking at (which also are the parameters I in-putted earlier when I opened that particular graph, the pic of this I posted in #115). So what is the purpose of that RowSource when I already have a graph on that particular report with its unique RowSource which corresponds to the functions it performs? Sorry if i'm sounding super dumb right now but I am trying to wrap my head around things

[June7]

So what is the purpose of that RowSource ...

Lost again. What is this referring to?

[Sonny101]

The one which you posted:

SELECT [enter start] AS StartDate, [enter end] AS EndDate, *
FROM tablename
WHERE DateField Between [enter start] And [enter end];

Perhaps if you have have time just modify one of the reports in the access file which I have posted so I can have a better understanding of what you are referring to

[June7]

From post 122:

The SQL example in my last post was for a report RecordSource using popup inputs.


I will look at the db later tonight.

[Sonny101]

hahaha oh shoot I mixed up RowSource with RecordSource

so the sql would be:

SELECT [[Forms]![Par-Pak final].[startdate]] AS StartDate, [[Forms]![Par-Pak final].[enddate]] AS EndDate, *
FROM Table1
WHERE Date Between [[Forms]![Par-Pak final].[startdate]] And [[[Forms]![Par-Pak final].[enddate]]];


PS Yeah I later realized I souldn't have used 'Date' for the date field as it is an universal constant, but couldn't change it up now in the table, as it will mess up the whole database

[June7]

Too many [ and ] in there.

The field name change will propagate to many places but not all.

I use Rick Fisher's Find and Replace for Access add-in for renaming. It's saved my sanity more than once. Cost about $50.

[Sonny101]

Did this:

SELECT [Forms]![Par-Pak final].[startdate] AS StartDate, [Forms]![Par-Pak final].[enddate] AS EndDate, *
FROM Table1
WHERE Date Between [Forms]![Par-Pak final].[startdate] And [Forms]![Par-Pak final].[enddate];

But now it asks me for the start date and end date 3 times

[June7]

Better put Date within [] so Access will be sure to understand it is a field and not the function.

I don't know why it is prompting 3 times. Doesn't do that to me.

[Sonny101]

Yeah first two times if I enter the date parameters and leave the third pop-up blank, i get no graph but get the start date and end date boxes with dates, but if I leave the first two pop-ups blank and enter date parameters in the third one, the graph shows, but the start date and the end date box in the report are blank. I suspect it is asking me first for the boxes and then again for the graph, it doesnt use the date parametrs i used for filtering for the boxes in the report. Ideas?

[June7]

Again, I NEVER use query popups. I reference controls on form. If the report RecordSource and the graph RowSource all reference controls of open form, there will not be ANY popups.