Sum of Amount

S

Shirley

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
 
O

Ofer Cohen

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.
 
S

Shirley

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?
 
O

Ofer Cohen

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];

--
Good Luck
BS"D


Shirley said:
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?
--
Shirley


Shirley said:
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
Click to expand...
Click to expand...
 
Top