how to set the default value to zero when the cell is blank

[Guest]

Hi,

I have a table with data imported from Excel. The table has a lot of blank
cells and I want the default value for those blanks to be zero, how do I do
that in Access?

Thanks.

 

[Jeff Boyce]

Vera

Why? As in "why do you want zeros, when the original data (in Excel) had
'nothings'?"

I have a suspicion you are trying to deal with Nulls, which you can easily
do in a query, using the Nz() function.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Yes, they are nulls, so how do I use the Nz() function in query to make those
blanks bull?

Thanks.

 

[Jeff Boyce]

Vera

Open a new query in design mode. Indicate the table you wish to use.
Select one of the fields you know to hold Nulls.

Now modify the "Field" contents, from something like:

YourTableFieldName

to:

Expr1: Nz([YourTableFieldName],0)

Repeat as needed...

Feel free to make the "Expr1" portion a more meaningful title for the field.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Jeff

thanks. I tried your suggestion and it works. Is there a way to do this
automatically for the whole table? that is, I would like to have codes whcih
will check to see if the field is "null" and then replace it with "zero"

regards, vera

Vera

Open a new query in design mode. Indicate the table you wish to use.
Select one of the fields you know to hold Nulls.

Now modify the "Field" contents, from something like:

YourTableFieldName

to:

Expr1: Nz([YourTableFieldName],0)

Repeat as needed...

Feel free to make the "Expr1" portion a more meaningful title for the field.

Regards

Jeff Boyce
<Office/Access MVP>

Yes, they are nulls, so how do I use the Nz() function in query to make
those
blanks bull?

Thanks.

 

[Jeff Boyce]

That'd be nice... nope, not that I'm aware of.

Regards

Jeff Boyce
<Office/Access MVP>

Jeff

thanks. I tried your suggestion and it works. Is there a way to do this
automatically for the whole table? that is, I would like to have codes
whcih
will check to see if the field is "null" and then replace it with "zero"

regards, vera

Vera

Open a new query in design mode. Indicate the table you wish to use.
Select one of the fields you know to hold Nulls.

Now modify the "Field" contents, from something like:

YourTableFieldName

to:

Expr1: Nz([YourTableFieldName],0)

Repeat as needed...

Feel free to make the "Expr1" portion a more meaningful title for the
field.

Regards

Jeff Boyce
<Office/Access MVP>

Yes, they are nulls, so how do I use the Nz() function in query to make
those
blanks bull?

Thanks.

:

Vera

Why? As in "why do you want zeros, when the original data (in Excel)
had
'nothings'?"

I have a suspicion you are trying to deal with Nulls, which you can
easily
do in a query, using the Nz() function.

Regards

Jeff Boyce
<Office/Access MVP>

Hi,

I have a table with data imported from Excel. The table has a lot of
blank
cells and I want the default value for those blanks to be zero, how
do
I
do
that in Access?

Thanks.