Graph the data from multiple fields

[June7]

Again, use a form for input of parameters. Report textbox ControlSource references control on form.

I never use dynamic popups in queries.

[Sonny101]

I dont really understand what you mean by form for the input of parameters. Arent the parameters 'start date' and 'end date' ? While on the form there is only a date field... Dont we input the parameters when it prompts us to do so when opening that particular report? Sorry if this is a dumb question

[June7]

I NEVER use dynamic parameter input popups in query. Can't validate user input. If they mistype the input, the query/report will still try to run.

I use UNBOUND controls on a form to input filter criteria. I can manage validation of the input. Only open report if input is correct (valid date or whatever). Then I reference those controls as parameter inputs.

Review http://www.datapigtechnologies.com/f...mtoreport.html

That example does not show date parameters, which must be handled a little differently, but concept of input on form is the same.

[Sonny101]

Oh i get it what you were trying to say, and yes that would be a better way for displaying reports. One thing however is that how would you bind the two text boxes to input the parameters? What would be the rowsource for the textboxes for one to input the start date and the other to input the end date?

The graph rowsource would then probably change to: (if I also include a textbox for tech name)
SELECT Category, Sum(Hrs) FROM UnionT1 WHERE [datefield] BETWEEN [datefield] BETWEEN [tbxStartDate] AND [tbxEndDate] AND Tech = [tbxTech]GROUP BY Category;

[June7]

The textboxes are UNBOUND. Textboxes don't have a rowsource. If you want comboboxes, the RowSource could be:
SELECT DISTINCT [datefield] FROM tablename ORDER BY [datefield] DESC;

What is the actual name of your datefield?

You have extra [datefield] BETWEEN in there.

[Sonny101]

I tried this: SELECT Table1.Date FROM Table1 GROUP BY Table1.Date ORDER BY Table1.Date DESC; for the comboboxes, with the report button set to fire up one of the reports, after clicking the report command button, it again asks me about the start date and end date (I want to input the values i put in the combo box )(see pic below). So how do I set in one combobox as start date, other as end date, so it doesnt ask me for those parameters again after I click on the command button. And then maybe multiple of these fields like this for the other graphs I have created to be put into one form.


PS Command0 starts up the report

[June7]

If there are popups it means Access can't find something. What are the actual names of the comboboxes? Are you referencing those names in query or report textboxes or in graph RowSource?

[Sonny101]

Yup changed the graph rowsource to SELECT Union3T1.Act, Sum(Hrs) FROM Union3T1 WHERE [Date] BETWEEN [tbxStartDate] AND [tbxEndDate] GROUP BY Union3T1.Act;

Actual names are Start Date and End Date

[June7]

If the names are Start Date and End Date why are you referencing tbxStartDate and tbxEndDate?

Advise not to use spaces in naming convention. Also, Date is a reserved word and should not use reserved words as field names.

[Sonny101]

Changed them to Start Date and End Date, but returns with a blank report :/ Leave all that to be dynamic inputs for the moment, is there an way to display Start Date and End Date as textboxes, when I view the graph for that specific time? (See pic below)

[June7]

As stated in post 106, textboxes on report can reference the form controls with expression in ControlSource property.

[Sonny101]

Shouldn't the expression (in ControlSource property of the textbox): =[Forms]![Par-Pak final].[startdate] display the date I typed in earlier as the start date?

[June7]

Yes.

Type date into UNBOUND textbox on form.

Textbox on report can reference the textbox on form.

Form must remain open when the report is opened.

There are other ways to pass values to report but that is probably the simplest.

[Sonny101]

I kinda scrapped the idea of having a separate form with combo boxes for start date and end date. It was giving too many errors and wasnt required that much. All I wanted to do was to save in the dynamically entered value for start date and end date earlier while opening the report, and then a text box which shows start date and end date. I think the method you are describing requires me to have the original form open with the start date and end date in that form. So any other way to store in the earlier entered value and then display it in the report?

[June7]

Yes, I did say the form must remain open. Why is that an issue? What errors? Other methods mostly still involve a form for input.

Here is only one I can think of that doesn't require the input form but will still make the inputs available for display on report. It still uses dynamic popups (I NEVER use them).

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