Subreport with date criteria that can generate reports

[maxmaggot]

Hi,


I have a subform on my main form that displays overall figures for amount spent in each of different catagories. The user can change the date criteria to update the months and year that can be displayed.

I want to have a button below this that generates a report based on the results from the subform. I'm trying to make this future proof and each text box in the subform uses a different query to get its values. This can be up to 10 different queries for a subform. So How do I get this to display in the report without a major amount of work.

Is there a simple way to do this?

[June7]

How does each textbox use a different query?

[maxmaggot]

Sorry misinfo. It uses a different sql statement to total the values

[June7]

Still not following. Show example of textbox ControlSource.

If you want to provide db, follow instructions at bottom of my post.

[maxmaggot]

Sorry for late reply. Looking back over my code i realised I had not even written the VBA code completely. The control source code for the text box is written into the control source box below to give the user a default value

=Nz(DSum("[Amount]","Expenses","[Expense Type]=1 And [InvoiceDate]>=DateSerial(Year(Date()), 1, 1)"),0)

The user can then use combo boxes to set the start and end month and year. I check for input errors and then I want to change the control source to display the total value between the two dates. Here is where I run into problems. This is the VBA I've been messing with to try and change the control source but I'm stuck now.

Me.txtTotalAdvertising.ControlSource = "Nz(DSum("[Amount]", "Expenses", "[Expense Type]=1 And [InvoiceDate]>=" _
                                & Me.txtExpensesStartDate.text & "[InvoiceDate]<=" & Me.txtExpensesEndDate.text & ")""

This throws an error about brackets which I'm working on but I'm not sure if this will actually produce the desired result or if it is possible to change the control source like this.

[June7]

Okay, that helps my understanding. Not exactly an SQL statement - this is domain aggregate function. Maybe the VBA is not needed. Try:

=Nz(DSum("Amount", "Expenses", "[Expense Type]=1 AND [InvoiceDate] BETWEEN #" & Nz([txtExpensesStartDate],1/1/1900) & "# AND #" & Nz([txtExpensesEndDate],12/31/2900) & "#"))

[maxmaggot]

Brilliant as always June. Thanks for this. It works with all the error checking I had entered and everything.