Results 1 to 6 of 6
  1. #1
    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #3
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    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
  •