Blank field problem at access 2007

[Art Vandaley]

Hi,

I have a query with 3 columns. 1st and 2nd columns are from a table that
includes numbers. 3rd column is for summation of 1st and 2nd columns. I have
no summation if a field is blank. For example, query gives a result
something like below:

1st_______2nd________3rd

10________20_________30
3_________5__________8
Blank______7__________Blank (must be 7 ???)
15________Blank_______Blank (must be 15 ???)


How can I solve this problem?

Thanks a lot in advance.

 

[Allen Browne]

Use Nz() around each of the numbers.

Presumably you have a calculated query field that looks like this:
Col3: [Col1] + [Col2]
Try:
Col3: Nz([Col1], 0) + Nz([Col2], 0)

For an explanation of what's going on see issue #2 in this article:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[Guest]

Hi Art,

You need to understand your table data. I assume the two columns are
defined in the table as numeric? If so your 'blank' is a Null.

1. Define default values for your table columns as 0 (zero) and update all
existing records. Your existing query should then work.

2. If you don't want that or can't change the table's metadata then use the
Nz function when summing col1 and col2. Your third column specification
should look something like the following.

Nz([col1],0) + Nz([col2],0)

Rod