Sum of Amount

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shirley - 08 Nov 2007 20:15 GMT

Hi,

I have a query which sums the invoiced amounts on a purchase order. If
there is no invoiced amounts, it leaves a blank field. How do I write the
query so that the blank field is replaced with a zero (0)?

Thanks,
Shirley

Signature

Shirley

Reply to this Message

Ofer Cohen - 08 Nov 2007 20:48 GMT

The Nz function will replace Null to Zero

Nz([FieldName],0)

That might not be the case, but from your post I can't see which tables
involved or what is the join between them.

If you need more help, please post the SQL.

Signature

Good Luck
BS"D

> Hi,
>
[quoted text clipped - 4 lines]
> Thanks,
> Shirley

Reply to this Message

Shirley - 08 Nov 2007 21:28 GMT

Thanks for the response, but it's not working. Here is the SQL:

SELECT [PO Query].Project, [PO Query].[PO No], [PO Query].[PO Date], [PO
Query].[Vendor Name], [PO Query].[Sum(amount)], [AP Query].[Sum Of Amount]
FROM [PO Query] LEFT JOIN [AP Query] ON [PO Query].[PO No] = [AP Query].[PO
No]
GROUP BY [PO Query].Project, [PO Query].[PO No], [PO Query].[PO Date], [PO
Query].[Vendor Name], [PO Query].[Sum(amount)], [AP Query].[Sum Of Amount];

Can you still help?

Signature

Shirley

> Hi,
>
[quoted text clipped - 4 lines]
> Thanks,
> Shirley

Reply to this Message

Ofer Cohen - 08 Nov 2007 21:56 GMT

Have you tried

SELECT [PO Query].Project, [PO Query].[PO No], [PO Query].[PO Date], [PO
Query].[Vendor Name], nz([PO Query].[Sum(amount)],0), Nz([AP Query].[Sum Of
Amount],0)
FROM [PO Query] LEFT JOIN [AP Query] ON [PO Query].[PO No] = [AP Query].[PO
No]
GROUP BY [PO Query].Project, [PO Query].[PO No], [PO Query].[PO Date], [PO
Query].[Vendor Name], [PO Query].[Sum(amount)], [AP Query].[Sum Of Amount];

Signature

Good Luck
BS"D

> Thanks for the response, but it's not working. Here is the SQL:
>
[quoted text clipped - 15 lines]
> > Thanks,
> > Shirley

Reply to this Message