Best way to set empty integer fields on Access table?

[lorirobn]

Hi,

I am setting up a table that has 4 integer fields. Most of the time these fields will not have any values. Currently they are 'null'. What is the best way to approach this - should I update each record to put a zero in these 4 fields?

In a program where I do an Insert onto the table, I am getting a Write Conflict error. I read on this forum that it is because of the nulls (possibly), and I am wondering if I should do a work-around, or put zeroes into the fields.

Would appreciate any pointers. I am a 'reformed' mainframe programmer, and so not sure the best path on Access tables.

Thanks in advance.

 

[mhartman1]

Hello:

One way would be to run an update query to reset the fields.

Regards
Mark

 

[lorirobn]

I just realized it's my one 'Money' field that's causing the problem, not my integer fields.

Mhartman, thanks for the response. Yes, I can run an update query to reset these fields. But I am curious about 'best practice'. Is it considered better to reset all the blank money fields with zeroes, rather than having blank values?

I am not well-versed on how Access tables store data; is it more efficient to have null values rather than zeroes? Is it poor practice to have null in a money field?

Thanks in advance,
Lori

 

[Melagan]

I think null values are just fine; they're a special type of value that you may want to test for in the future. In the case of displaying data in a query, form, or report, there is always one of my favorite functions, Nz().

From Access 2003 helpfile:

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Syntax: Nz(required variant, optional[Value if Null])

[COLOR=green]'Example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.[/color]

varResult = Nz(varFreight, "No Freight Charge")

~Melagan
______
"It's never too late to become what you might have been.

 

[JerryKlmns]

lorirobn,

How about a default value of 0 on those fields?
You could run an update query to set NULL to 0
If you keep NULL then on calculated fields or filtering records you 'll need NZ(). If you retrieve data outside from Access then NULL could be a pain.

 

[lorirobn]

Hi - thanks for the responses, and for the tips.

Melagan, you say null values are just fine, so that helps. I will decide if I am going to leave nulls or replace them with defaults. I am curious to hear what most people do, though - leave nulls, or set default to 0?



Thanks,
Lori

 

[PHV]

Null means unknown.
0 means, eh, zero.
You have to understand the difference and make your choice.
Note: Null and Zero are not handled the same way by any aggregate function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lorirobn]

Helps a bunch. Thanks for the clarification, PH.