Automatically populating null fields in zeroes in an acces table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 30 May 2008 21:25 GMT

I would like to know how I could construct a query or function that would
automatically add zeros to cells that have are empty (null value). For
example, if customer X has zero sales in May how can I populate the cell with
a zero instead of leaving the cell empty?

Thanks.

Signature

Bob

Reply to this Message

Klatuu - 30 May 2008 22:08 GMT

In the query builder for your update query, put this in the Update To line
for each numberic field you want to contvert:

FieldName: SomeNumber

UpdateTo: Nz([SomeNumber],0)

The Nz function returns the first argument is it not null. If it is null,
it returns the value of the second argument.

Signature

Dave Hargis, Microsoft Access MVP

> I would like to know how I could construct a query or function that would
> automatically add zeros to cells that have are empty (null value). For
> example, if customer X has zero sales in May how can I populate the cell with
> a zero instead of leaving the cell empty?
>
> Thanks.

Reply to this Message

Bob - 30 May 2008 22:45 GMT

Thanks. Works perfectly.

Signature

Bob

> In the query builder for your update query, put this in the Update To line
> for each numberic field you want to contvert:
[quoted text clipped - 12 lines]
> >
> > Thanks.

Reply to this Message

Jim Burke in Novi - 31 May 2008 15:27 GMT

There may be a reason you're not doing this, but you may want to consider
changing the default value for that field to 0 in the table definition.

Reply to this Message