Calculation problem in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

there are over 10,000 data in the table named z003.
I tried to work out the flollowing calculation [pcs]*[pack]+[odd]. All data
in these three fields are in numuric format. After running the query, most of
data came out expected results. But 339 data came out null. I tried to export
these data to Excel. They are calculatable.
I tried to seperate the formular to 2 part. [pcs]*[pack] worked out fine.
But it cannot work out after adding the field [odd].
All data in this table is crearted by a query linked to a Sybase database.
 
i find out some hints. in these 339 data, either [psc] or [odd] is null. is
it possible to calculate if a field is null?
 
i dont think so

try nz([pcs]) *nz(field2) + nz(field3) etc
--
Regards


Patrick Stubbin


Dennis Cheung said:
i find out some hints. in these 339 data, either [psc] or [odd] is null. is
it possible to calculate if a field is null?
--
Dennis Cheung


Dennis Cheung said:
there are over 10,000 data in the table named z003.
I tried to work out the flollowing calculation [pcs]*[pack]+[odd]. All data
in these three fields are in numuric format. After running the query, most of
data came out expected results. But 339 data came out null. I tried to export
these data to Excel. They are calculatable.
I tried to seperate the formular to 2 part. [pcs]*[pack] worked out fine.
But it cannot work out after adding the field [odd].
All data in this table is crearted by a query linked to a Sybase database.
Click to expand...
Click to expand...
 
there are over 10,000 data in the table named z003.
I tried to work out the flollowing calculation [pcs]*[pack]+[odd]. All data
in these three fields are in numuric format. After running the query, most of
data came out expected results. But 339 data came out null. I tried to export
these data to Excel. They are calculatable.
I tried to seperate the formular to 2 part. [pcs]*[pack] worked out fine.
But it cannot work out after adding the field [odd].
All data in this table is crearted by a query linked to a Sybase database.
Click to expand...

Any expression involving a NULL returns the value NULL. You can use
the NZ() function to convert a NULL to zero (by default), or to any
value you wish (using the optional second argument).

One critical question here: what is the appropriate calculated value
if [pack] - or any of these fields - is null? NULL means "this value
is unspecified, undefined, unknown" - what is the product of 10 pcs
times an unspecified, undefined, unknown number? 10? 10000000? 32870?

IF you can assume that [odd] should always be treated as zero if it's
unknown, and that both [pcs] and [pack] should be treated as equal to
1, use

Nz([pcs], 1) * Nz([pack], 1) + NZ([odd])


John W. Vinson[MVP]
 
Back
Top