# Thread: MkTable problem if null need a Zero

1. Registered User
Join Date
Mar 2003
Posts
225

## Unanswered: MkTable problem if null need a Zero

I have a query (Maketable) that i am trying to run that totals a number of figures then dumps the total in the new table.

here is my calculation,
Code:
```SELECT Sum(Nz([tblEvents]![MinorStop],0)+Nz([tblEvents]![MajorStop],0)+Nz([tblEvents]![Breakdowns],0)+Nz([tblEvents]![CIP],0)+Nz([tblEvents]![ProductChange],0)+Nz([tblEvents]![Maintenance],0)) AS Total INTO Mk_PETakeout
FROM tblEvents INNER JOIN TblCodes1 ON tblEvents.EventCode = TblCodes1.EventCode
WHERE (((TblCodes1.EventCodeType)="lack of materials") AND ((TblCodes1.PlannedStop)=True) AND ((tblEvents.DayCode) Between [Forms]![OEEModel]![Start] And [Forms]![OEEModel]![End]) AND ((tblEvents.Line)=[Forms]![OEEModel]![Line]));```
this works fine as long as there is a figure present, if there is no data available there is no figure dumped to the new table....

if there is no total due to the lack of data then i want the new table data to to updated with a Zero...

how can i do this please

Cheers

Andy

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you could handle the null problem in subsequent operations in the table / form / report with an iif(), or another nz()

you may even be able to enclose the whole expression with nz
Code:
```SELECT nz(Sum(Nz([tblEvents]![MinorStop], 0) + Nz([tblEvents]![MajorStop], 0) + Nz([tblEvents]![Breakdowns], 0) + Nz([tblEvents]![CIP], 0) + Nz([tblEvents]![ProductChange], 0) + Nz([tblEvents]![Maintenance], 0)), 0) AS Total INTO Mk_PETakeout
FROM tblEvents INNER JOIN TblCodes1 ON tblEvents.EventCode = TblCodes1.EventCode
WHERE (((TblCodes1.EventCodeType)="lack of materials") AND ((TblCodes1.PlannedStop)=True) AND ((tblEvents.DayCode) Between [Forms]![OEEModel]![Start] And [Forms]![OEEModel]![End]) AND ((tblEvents.Line)=[Forms]![OEEModel]![Line]));```
you could run a second query to update nulls to zero
apart from some financial reporting I find the distinction between NULL and zero important and worth retaining

3. Registered User
Join Date
Mar 2003
Posts
225
ok i think your last sugestion would work....(Hopefully)

the one to run another macro to populate all nulls with a zero..

Could you tell me how to do that please

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
did you try the nz around the sum expression first off?
- that would probably be easier.

the update statement would be something like

Code:
`update mytable set mycolumn=0 where mycolumn=NULL;`
youmay need totinker witht the =NULL part, some db's require isnull(mycolumn)

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by Chimp8471
this works fine as long as there is a figure present, if there is no data available there is no figure dumped to the new table....
Hi

Sorry to butt in but - by no data do you mean that if you remove the Make Table bit (i.e. change to a simple select) there is no data as oppossed to a row with a Null value? If the former, perhaps altering the inner join to an outer would do it?

HTH

6. Registered User
Join Date
Mar 2003
Posts
225
if i change the query type to Select, i do get as you quite rightly guessed "nothing"

which is technically right, but i need a zero for a later calculation to work.

I have tried changing the Join to what you suggeted but no joy...

if i could replace the empty cells with the Zero's i am sure that would work...

Actually just used the replace zero's query and works a treat thanks for your help

Andy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•