Sum of ONE of my fields will not display -- why

J

jwr

This is the SQL from my Accounts Aging Report. I am able to get a grand
total for Current, 31-60 Days, 61-90 Days and Balance. But NOT for 91+ Days.
I have checked to see if the field if visible, and it is. I do not see why
it will not work. I have deleted that field and re-entered several times,
but still get no grand total for 91+ Days. Thank you for your assistance.


SELECT DISTINCTROW
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61 And
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days], Sum(IIf((Date()-[ShipDate])<1000 And
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments])) AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
 
J

Jeff Boyce

What shows up in the underlying query?

Jeff Boyce
<Access MVP>

jwr said:
This is the SQL from my Accounts Aging Report. I am able to get a grand
total for Current, 31-60 Days, 61-90 Days and Balance. But NOT for 91+ Days.
I have checked to see if the field if visible, and it is. I do not see why
it will not work. I have deleted that field and re-entered several times,
but still get no grand total for 91+ Days. Thank you for your assistance.


SELECT DISTINCTROW
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61 And(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days], Sum(IIf((Date()-[ShipDate])<1000 And
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments])) AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Click to expand...
 
J

jwr

Patrick -- There was no information in your message.

Jeff -- the underlying query supplies the correct detail information just as
the report does.
Thanks,JR

Jeff Boyce said:
What shows up in the underlying query?

Jeff Boyce
<Access MVP>

jwr said:
This is the SQL from my Accounts Aging Report. I am able to get a grand
total for Current, 31-60 Days, 61-90 Days and Balance. But NOT for 91+ Days.
I have checked to see if the field if visible, and it is. I do not see why
it will not work. I have deleted that field and re-entered several times,
but still get no grand total for 91+ Days. Thank you for your assistance.


SELECT DISTINCTROW
Click to expand...
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61 And
Click to expand...
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
Click to expand...
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days], Sum(IIf((Date()-[ShipDate])<1000 And
Click to expand...
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments])) AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Click to expand...
Click to expand...
 
J

Jeff Boyce

see in-line

jwr said:
Patrick -- There was no information in your message.

Jeff -- the underlying query supplies the correct detail information just as
the report does.
Click to expand...

I thought your first post said the report does not supply the detail info.

Jeff Boyce
Thanks,JR

Jeff Boyce said:
What shows up in the underlying query?

Jeff Boyce
<Access MVP>

see
why
Click to expand...
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current],
Click to expand...
Sum(IIf((Date()-[ShipDate])<61
And
Click to expand...
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
Click to expand...
Click to expand...
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days], Sum(IIf((Date()-[ShipDate])<1000 And
Click to expand...
Click to expand...
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Click to expand...
Payments]))
AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Click to expand...
Click to expand...
Click to expand...
 
J

jwr

I get detail for all fields, including 91+ days, and grand totals for all
columns except the 91+ days column.

I cannot see why the 91+ days column does not total. It is set up the same
as the other column totals and is marked visible - yes.
JR

Jeff Boyce said:
see in-line

jwr said:
Patrick -- There was no information in your message.

Jeff -- the underlying query supplies the correct detail information
Click to expand...
just
as
the report does.
Click to expand...

I thought your first post said the report does not supply the detail info.

Jeff Boyce
Thanks,JR
Click to expand...
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61
And
Click to expand...
Click to expand...
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
Click to expand...
Click to expand...
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days], Sum(IIf((Date()-[ShipDate])<1000 And
Click to expand...
Click to expand...
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]))
AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'm fresh out of ideas on it, too.

If the SQL statement returns actual data and the report doesn't display it,
perhaps you need to "junk" the report and create a new one?

Regards

Jeff Boyce
<Access MVP>

jwr said:
I get detail for all fields, including 91+ days, and grand totals for all
columns except the 91+ days column.

I cannot see why the 91+ days column does not total. It is set up the same
as the other column totals and is marked visible - yes.
JR

Jeff Boyce said:
see in-line

just

I thought your first post said the report does not supply the detail info.

Jeff Boyce
not
see
Click to expand...
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61
And
Click to expand...
Click to expand...
Click to expand...
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
Click to expand...
Click to expand...
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [61-90 Days],
Click to expand...
Sum(IIf((Date()-[ShipDate])<1000
And
Click to expand...
(Date()-[ShipDate]>90),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
tal Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]))
AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Aging Receivables Not working 16
Return repeats info in "8s" 14
Aging Date in General Templates Order Entry db 0
Dealing with Pecentage Calculations in Reports 0
conditional sum needed? sums not totaling correctly... 2
Calculation problem fromSQL 0
Subreport not working 3
Report totals off by a couple cents. 4

Top