Form pulls totals from query

[seth.murphine]

I have a form... ugh don't we all... anyways, this form has a combo box we'll call it [username], I have a [date_start] and a [date_end] text box below it (not that placement matters).



I have a query too... that the form is pulling information from, I have it so the query pulled reflects the value of whatever or I guess whomever is in [username], but for some reason I have the below expression in the criteria for the query column we'll call it [date]:

between "#" & Format([Forms]![dateform]![date_start], "MM/DD/YYYY") & "#" AND "#" & Format([Forms]![dateform]![date_end], "MM/DD/YYYY") & "#"

Well to no avail I can not think around this, the query finds this expression to large. So I wrote a filter in VBA on click it would do the following:

Me.Filter = "adIDate between #" & Format([Forms]![yp_ipr_ipm]![date_start], "MM/DD/YYYY") & "# AND #" & Format([Forms]![yp_ipr_ipm]![date_end], "MM/DD/YYYY") & "#"
Me.Filter = "[strEmp] Like '*" & [Forms]![dateform]![username] & "*'"
Me.FilterOn = True

Yet now I came across another problem that is totals, the fields that I have are all check boxes, fixed the sql so the -sum showed the trues as 1's, but my problem is that it is not totaling all the 1's up in the fields that I have set.

Help!! lol

Thanks in advance my brain is burnt and I just can't think around stuff right now.

[June7]

Post the sql statement for analysis or provide the project itself. Follow instructions at bottom of my post.

[seth.murphine]

Here is the SQL statement:

SELECT yp_adv_data.ID, yp_team_ipm.strEmp, yp_adv_data.adIDate, -Sum([yp_adv_data].[adBox9]) AS SumOfadBox9, -Sum(yp_adv_data.ad216) AS SumOfad216, -Sum(yp_adv_data.adIBRep) AS SumOfadIBRep, -Sum(yp_adv_data.adOBRep) AS SumOfadOBRep, -Sum(yp_adv_data.adEmailChk) AS SumOfadEmailChk, -Sum(yp_adv_data.adSave) AS SumOfadSave, -Sum(yp_adv_data.adSaved) AS SumOfadSaved, -Sum(yp_adv_data.adReCall) AS SumOfadReCall, -Sum(yp_adv_data.adSME) AS SumOfadSME, -Sum(yp_adv_data.adSMEA) AS SumOfadSMEA, -Sum(yp_adv_data.adSMS) AS SumOfadSMS, -Sum(yp_adv_data.adSMSTA) AS SumOfadSMSTA, -Sum(yp_adv_data.adSMST) AS SumOfadSMST, -Sum(yp_adv_data.adIBCalls) AS SumOfadIBCalls, -Sum(yp_adv_data.ad30d) AS SumOfad30d, -Sum(yp_adv_data.adCA) AS SumOfadCA, -Sum(yp_adv_data.adPro) AS SumOfadPro, -Sum(yp_adv_data.adProA) AS SumOfadProA, -Sum(yp_adv_data.adSCall) AS SumOfadSCall, Sum(yp_adv_data.adRevCol) AS SumOfadRevCol, Sum(yp_adv_data.adNewRev) AS SumOfadNewRev, Sum(yp_adv_data.adReRev) AS SumOfadReRev, Sum(yp_adv_data.adImpRev) AS SumOfadImpRev
FROM yp_team_ipm INNER JOIN yp_adv_data ON yp_team_ipm.strEmp = yp_adv_data.strEmp
GROUP BY yp_adv_data.ID, yp_team_ipm.strEmp, yp_adv_data.adIDate
HAVING (((yp_team_ipm.strEmp) Like [forms]![yp_teamli]![ipm_member]) AND ((yp_adv_data.adIDate) Between [forms]![yp_teamli]![date_start] And [forms]![yp_teamli]![date_end]));

In the fields on the form it is not totaling the records it is only showing the value of the first record.

[seth.murphine]

Never mind figured out what I was doing wrong, like I said my brain is dying here and I'm trying to keep it alive.

I needed:

=sum(sumoffieldname)

instead of

=sumoffieldname

Thanks