Transforming empty fields into "0" in a query

[Guest]

Hi!

I got a question concerning a query.
In this query I combine columns from several tables and other queries.
Besides that there is a calculation in this query for a new column. The
simplified query result looks like this:

Column1, Column2, Column3
3, 5, 8
2, 8, 10
2, Null, Null

So what column3 does is counting up column1 and 2. Though, column2 sometimes
contains empty fields. This means that column3 can't make a correct
calculation anymore.
What I want to reach is that in column2 "Is Null" values will be transformed
into "0". If that's done the calculation will be possible and the third row
will result in 2+0=0.

I've tried to reach this by running an update query before running this
query, something like this:

UPDATE Query1 SET Query1.Column2 = 0
WHERE ((Query1.Column2=Null));

Although this results in the error: "operation must use an updateable query".

So, could someone help me? Is the UPDATE query idea the best solution (if
this can be fixxed) or are there better solutions.

Thanks in advance!

- Onne (NL)

 

[Nikos Yannacopoulos]

Onne,

Instead of using Column2 as is in your query, use a calculated field in
its place, like:

Column2_Alias: Val(Nz([Column2],0))

Function Nz will return the second argument (0 in this case) when it
finds a Null. The only problem is it will return a string instead of a
number, therefore you use the Val function on it to convert back to numeric.

HTH,
Nikos

 

[Guest]

Hi Nikos,

this worked out fine!
This way of solving the problem is much more beautifull then using an extra
query.
Thanks a lot for your help!

Greetings Onne

Onne,

Instead of using Column2 as is in your query, use a calculated field in
its place, like:

Column2_Alias: Val(Nz([Column2],0))

Function Nz will return the second argument (0 in this case) when it
finds a Null. The only problem is it will return a string instead of a
number, therefore you use the Val function on it to convert back to numeric.

HTH,
Nikos

Hi!

I got a question concerning a query.
In this query I combine columns from several tables and other queries.
Besides that there is a calculation in this query for a new column. The
simplified query result looks like this:

Column1, Column2, Column3
3, 5, 8
2, 8, 10
2, Null, Null

So what column3 does is counting up column1 and 2. Though, column2 sometimes
contains empty fields. This means that column3 can't make a correct
calculation anymore.
What I want to reach is that in column2 "Is Null" values will be transformed
into "0". If that's done the calculation will be possible and the third row
will result in 2+0=0.

I've tried to reach this by running an update query before running this
query, something like this:

UPDATE Query1 SET Query1.Column2 = 0
WHERE ((Query1.Column2=Null));

Although this results in the error: "operation must use an updateable query".

So, could someone help me? Is the UPDATE query idea the best solution (if
this can be fixxed) or are there better solutions.

Thanks in advance!

- Onne (NL)

 

[Nikos Yannacopoulos]

Welcome!