Sum Query

[Seth Schrock]

Try

WHERE (Master.COMPLETEDINFIELD) Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd

[didacticone]

still no luck my friend

[Seth Schrock]

Okay, we are going to have to try something to get that field to be recognized. First remove your WHERE clause and then open the query in Design view (where you see the table(s) that the query is based on). Now, find the COMPLETEDINFIEL D field in the table and double click on it. This should add it to the list. Uncheck the Show checkbox. Now in the criteria field, enter Between Forms!frmSearch !txtStart And Forms!frmSearch !txtEnd . See if that does it.

[didacticone]

still coming up and now the report isnt grouped by job type its grouped by the date

[Seth Schrock]

It is grouping by the date now because it has been added to the GROUP BY clause. One more thing that I have thought of. In the query properties, there is a property called parameters. Make sure that there isn't anything in there.

If that doesn't work, I'm stumped as to why that popup keeps appearing. You would probably have to then make your query not an aggregate query and then do your summing and filtering in the report.

[didacticone]

I am glad it is not just me who is baffled by this... if i was to do it in the report can you give me some help on that? i have never done and filtering in a report itself. thank you for your help

[Seth Schrock]

You would just make the control source of each control in the report have the sum function in it. Like this

=Sum(IIf([master.REPAIR],1,0))

You would then set your report's filter property using VBA, probably in the On_Open event.

Me.Filter = Me.OpenArgs

You just need to setup your filter string when you open the report that you can pass it the correct value which is the same as your WHERE clause without the word WHERE.

[didacticone]

i changed the control sources for all the fields. just confused as to how to put the where clause.

[Seth Schrock]

You would remove the WHERE clause from the query.

[didacticone]

my query is now as follows:

SELECT Master.JOBTYPE, Master.COMPLETEDINFIELD, Master.REPAIR, Master.REPLACE, Master.REMOVE, Master.INSTALL, Master.MAINTENANCE, Master.TEMPDISCONNECT
FROM Master;

[Seth Schrock]

That is correct. When you run it, do you get any popups?

[didacticone]

no pop ups, but the job type is not grouped in the report, and i am not sure how to query the dates i want.

[paulwilliam024]

There are some intrusting and i like this..

[Seth Schrock]

You can add groupings in the report design view. In Access 2010, it is in the Design tab, Grouping & Totals section (toward the left side).

To get the dates that you want I'm now thinking that you could use the WhereCondition argument when opening the report instead of the OpenArgs.

DoCmd.OpenReport ReportName:="yourReport", WhereCondition:="COMPLETEDINFIELD BETWEEN #" & Me.txtStart & "# And #" & Me.txtEnd & "#"

[didacticone]

Im having all sorts of problems here, i was able to group by job type, but the summation is all wrong. it is just adding and showing the same number for every job type. so for instance if there are 3 total repair jobs, 1 for 3 different categories, it is showing 3 in every category? again im sorry ive never worked with reports like this before.