Help with unweildy query

[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

 

[John W. Vinson]

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

If you're building up a query from multiple form controls (cbobuyer,
cbosupplier, etc.), you may want to build the SQL of a query in VBA code. Only
include a clause for a field if that field in fact has a filter.

One minor point - you don't need to wrap a DCount() function in CInt() -
DCount() already returns an integer.

If all of this stuff is in ONE CALCULATED FIELD then you should probably
consider writing a custom VBA function to extract this sum.

John W. Vinson [MVP]

 

[John Ortt]

On Mon, 13 Aug 2007 14:58:45 +0100, "John Ortt"

If you're building up a query from multiple form controls (cbobuyer,
cbosupplier, etc.), you may want to build the SQL of a query in VBA code.
Only
include a clause for a field if that field in fact has a filter.

One minor point - you don't need to wrap a DCount() function in CInt() -
DCount() already returns an integer.

If all of this stuff is in ONE CALCULATED FIELD then you should probably
consider writing a custom VBA function to extract this sum.

John W. Vinson [MVP]

Thanks John,

I was coming to the same conclusion myself. Just means I have to dust off
my VB skills

Thanks for the feedback.

Regards,

John