Access 2007 Update Query With Blank Fields

[MarkGardner]

I am having trouble with an update query where I am attempting to add or combine several fields together into another field. Nothing calcs for some fields. If I recall, the problem is because at least one of the fields has blanks rather than zeroes.

Somewhere I recall that in that case I need to place a null characteristic somewhere in the property characteristics. The problem is that I am not sure and I do not recall which property it belongs in and if the null property requires special formatting. I think that it belongs somewhere in the table structure properties. I cannot find anything on the books.

Thanks for any help to understand the null better.

MG


Thank you for your interest in helping. I have written dBase code for 20 years and I find SQL frustrating to me. It is almost like Greek to me, even though I have spent some time with it. I am trying to do this entirely through the wizards and the like.

Here is the SQL from the query.


UPDATE STMTS SET STMTS.NETCHRG = Round([STMTS]![TOTALS],2)+Round([STMTS]![TRANSFERS],2)-Round([STMTS]![POOLCR],2)-Round([STMTS]![FOCREDIT],2)+Round([STMTS]![ADD_WTR],2)+Round([STMTS]![WINTER_WTR],2), STMTS.GROSSDUE = Round([STMTS]![OTHERDUE],2)+Round([STMTS]![INSTDUE],2)+Round([STMTS]![PENALTY],2)+Round([STMTS]![INTEREST],2)+Round([STMTS]![FWD_PRYRYR],2)+Round([STMTS]![FAA_PRYRYR],2)+Round([STMTS]![KWB_DISTRB],2), STMTS.AMTDUE = Round([STMTS]![GROSSDUE],2)-Round([STMTS]![PREVPAID],2), STMTS.UNPDTOT = Round([STMTS]![AMTDUE],2)+Round([STMTS]![NETCHRG],2)-Round([STMTS]![INSTDUE],2), STMTS.GRANDTOT = Round([STMTS]![AMTDUE],2)+Round([STMTS]![PRIORYRS],2);

I thought that it might have something to do with the characteristics of the tables "STMTS" or "STMT SUMMARY".

Mark

 

[EMP]

Need more info. Please copy and paste the SQL statement of your update query here.

^

 

[georgedwilkinson]

Look at the NZ() function in Access help. It will allow you to treat a null value as whatever value you prefer.

Or, just post your sql here and somebody will give you pointer.