J
John Ortt
Hi Everyone,
I created a query which calculates a running sum for me but when I try to
add criteria to filter the results the string is too large.
Below is an example of the code I am trying to use for one field:
RaisedTot: Sum(CInt(DCount("[Query Number]","[tblQueryInfo]","
([forms].[frmreportfilters].[cbosite]="ALL") OR (Left([Query
Number],2)=[forms].[frmreportfilters].[cbosite]) and
(tblQueryInfo.[Post GR Number] Is Null And
[forms].[frmreportfilters].[cbostatus]="1") OR (tblQueryInfo.[Post GR
Number] Is Not Null And [forms].[frmreportfilters].[cbostatus]="2") and
(tblQueryInfo.[Buyer Name]=[forms].[frmreportfilters].[cbobuyer] Or
[forms].[frmreportfilters].[cbobuyer]=" NO FILTER") and
(tblQueryInfo.[Supplier]=[forms].[frmreportfilters].[cbosupplier] Or
[forms].[frmreportfilters].[cbosupplier]=" NO FILTER") and
(tblQueryInfo.[Project]=[forms].[frmreportfilters].[cboproject] Or
[forms].[frmreportfilters].[cboproject]=" NO FILTER") and
((tblQueryInfo.[Query Reason 1]=[forms].[frmreportfilters].[cboreason] Or
[forms].[frmreportfilters].[cboreason]=" NO FILTER") OR
(tblQueryInfo.[Query Reason 2]=[forms].[frmreportfilters].[cboreason] Or
[forms].[frmreportfilters].[cboreason]=" NO FILTER")) and
(month([Date Raised])<=" & Month([theMonth]) & " and
year([Date Raised])=" & Year([theMonth]) & " or
year([Date Raised])<" & Year([theMonth]) & ")")))
I want two similar fields (raisedTot and closedTot) but at the moment Access
says the string is too long and concatenates it which ruins the query.
Can anyone think of a more elegant way do do what I am trying to do?
Thanks in advance,
John
I created a query which calculates a running sum for me but when I try to
add criteria to filter the results the string is too large.
Below is an example of the code I am trying to use for one field:
RaisedTot: Sum(CInt(DCount("[Query Number]","[tblQueryInfo]","
([forms].[frmreportfilters].[cbosite]="ALL") OR (Left([Query
Number],2)=[forms].[frmreportfilters].[cbosite]) and
(tblQueryInfo.[Post GR Number] Is Null And
[forms].[frmreportfilters].[cbostatus]="1") OR (tblQueryInfo.[Post GR
Number] Is Not Null And [forms].[frmreportfilters].[cbostatus]="2") and
(tblQueryInfo.[Buyer Name]=[forms].[frmreportfilters].[cbobuyer] Or
[forms].[frmreportfilters].[cbobuyer]=" NO FILTER") and
(tblQueryInfo.[Supplier]=[forms].[frmreportfilters].[cbosupplier] Or
[forms].[frmreportfilters].[cbosupplier]=" NO FILTER") and
(tblQueryInfo.[Project]=[forms].[frmreportfilters].[cboproject] Or
[forms].[frmreportfilters].[cboproject]=" NO FILTER") and
((tblQueryInfo.[Query Reason 1]=[forms].[frmreportfilters].[cboreason] Or
[forms].[frmreportfilters].[cboreason]=" NO FILTER") OR
(tblQueryInfo.[Query Reason 2]=[forms].[frmreportfilters].[cboreason] Or
[forms].[frmreportfilters].[cboreason]=" NO FILTER")) and
(month([Date Raised])<=" & Month([theMonth]) & " and
year([Date Raised])=" & Year([theMonth]) & " or
year([Date Raised])<" & Year([theMonth]) & ")")))
I want two similar fields (raisedTot and closedTot) but at the moment Access
says the string is too long and concatenates it which ruins the query.
Can anyone think of a more elegant way do do what I am trying to do?
Thanks in advance,
John