Query- Group By - Sum - Null Values (need to display 0.0) ....Arghhh

[jimmy.guilfoyle]

Hi Access Gurus

Something is driving me bloody mad… no its not the wife.

I have a query whish uses the group by function and sums up based on some invoicing figures (currency)

Now my issue is that I need the sum to show a value of 0.00 if indeed there are no records. (from the Invoicing table)

Is this possible. I guess the query is working as it should is its sees no records to sum up. Its causing issues on my reports.

Appreciate if anyone can assist as this is causing me to have to create records with a value of zero so that it works properly and then it means I have a record that I don’t really want.

Thanks

Jimmy


See snapshot of the query below

 

[Galaxiom]

Use the Nz function. It converts Nulls to zeros.

 

[jimmy.guilfoyle]

Use the Nz function. It converts Nulls to zeros.

Hi Galaxiom .... Where exactly should I use this expression?

In the Query itself?

Been messing around creating new expressions referencing fields, searching the www etc...

Driving me mad..

Thanks for your advice.... can I have some more

Thanks

Jimmy

 

[jimmy.guilfoyle]

mmm Perhaps I have misled you as Ive been having a think.

I am trying to complete a calculation based upon data within records.

I am trying total (SUM) how much money has been invoiced. In my situation there are actually no records i.e no invoicing, so will this NZ function work?

 

[vbaInet]

.... can I have some more

Here's a good resource:

http://www.techonthenet.com/access/functions/

 

[Galaxiom]

In the field section of the query enter
[InvoiceTotals]:Sum(Nz([Invoice Amount]))

Your table name suggests you might have some less than optimum design issues.

Normally the in progress invoices would be in the same table as the finished ones but with a Staus field to show their progress. Quotes are usually treated similarly.

Also suggest you drop spaces from your object names. They just add unnecessary work.

 

[jimmy.guilfoyle]

Your table name suggests you might have some less than optimum design issues. I agree.... I should know better.

Normally the in progress invoices would be in the same table as the finished ones but with a Status field to show their progress. Quotes are usually treated similarly. I work for a company that uses SAP but it does not give simplistic reporting across the PMO department. My DB is very simple, all its really concerned about is Project Progress, Invoicing and variations.

From where I'm sitting (Project manager) an Invoice that I submit always follows the status of the Project e.g If a Project is "In Progress" my Invoice status is In Progress and If a Project is closed the Invoice is closed as its upto accounts to chase payment. I just need to know what has been raised as far as invoicing is concerned. Im sure someone with more experience would create something different from me but Im doing the job of 10 people (UAE Company....don't ask ) and this is a sideline for me as I have some exposure in the past from access and I realise that my departments needs a simple reporting tool.


Also suggest you drop spaces from your object names. They just add unnecessary work.[/quote] I know this is a very bad way of doing things



I still cannot get this bloody thing to work... Ive created a simple sort of replica.

A table for invoicing and a query grouping by and summing.

The table has no records and this is my issue, I want to display 0 for the query...

Can you look at this to assist.

Thanks again