Can't take the max of three columns with if function...

[SupperDuck]

Hi all.

I want to make a query. My query works. But when i wanto take max of the
three columns, it gives some errors (works but not with errors).

My query is like this.

http://img399.imageshack.us/img399/9467/myquery1oc.jpg


The column in red box is where i want to calculate and the pink ones, are
the elements of the query. And my function is like;

Kum Program:
IIf(NZ([BAKIYE_FORECAST]![FORECAST_PRG])>(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![QT_CUMPROGRAM]));IIf(NZ([BAKIYE_FORECAST]![FORECAST_PRG])>(NZ([830
PROGRAMLARI]![830 KUM PRG]));(NZ([BAKIYE_FORECAST]![FORECAST_PRG]));(NZ([830
PROGRAMLARI]![830 KUM
PRG])));IIf(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![QT_CUMPROGRAM])>(NZ([830
PROGRAMLARI]![830 KUM
PRG]));(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![QT_CUMPROGRAM]));(NZ([830
PROGRAMLARI]![830 KUM PRG]))))

When i run the query it gives an error (or lets say an input box, but it’s
an error for me ïŠ)like;

http://img399.imageshack.us/img399/5414/error18qt.jpg

and then this;

http://img283.imageshack.us/img283/9885/error24gy.jpg

And my query works when i click on ok.

But, when i don’t use this column, i mean when i don’t try to get the max of
the columns, columns forecast_prog and kum_prg have non zero items, but when
i start using the functions all of the cells in that two columns are zero.

I wonder why?

And how can i solve this?

Thanx a lot...

 

[Jeff Boyce]

A couple things to consider...

Your use of the Nz() function doesn't appear to have a "use this value if
null" component. By that I mean the syntax for Nz() is something like:

Nz([CheckThisVariable], "Use this value if the variable was null")

as a way to return a value when the variable was null. I suspect you are
trying to compare nulls to nulls, which won't happen!

On another point, if you have multiple columns containing data that you are
trying to compare (to find the maximum value among them), there's a chance
that you have ... a spreadsheet, not a relational database table! If you
describe a bit more about the data that's in the three columns and why you
have it spread across three columns rather than in a single column, the
'group readers may be able to offer alternate approaches.

 

[SupperDuck]

Hi Jeff,

First, thanx for replying.

I think i forgot to use “; 0†in NZ, i will change this and try again. But i
think that’s not the source of problem.

I have 3 tables. For the forecast of selling an x product, i have 3 numbers.
And i calculate them in some make table queries. For some products, they can
have 2 forecast, and i must make the null value “0â€. And the one you see in
the picture is my final query. So i take 3 columns from that tables and
trying to compare, which one is biggest.

In my final table i must see the three values and the maximum of that values.

If it is easier, i can make this a new table, then will compare that 3
columns.

Thanx..

A couple things to consider...

Your use of the Nz() function doesn't appear to have a "use this value if
null" component. By that I mean the syntax for Nz() is something like:

Nz([CheckThisVariable], "Use this value if the variable was null")

as a way to return a value when the variable was null. I suspect you are
trying to compare nulls to nulls, which won't happen!

On another point, if you have multiple columns containing data that you are
trying to compare (to find the maximum value among them), there's a chance
that you have ... a spreadsheet, not a relational database table! If you
describe a bit more about the data that's in the three columns and why you
have it spread across three columns rather than in a single column, the
'group readers may be able to offer alternate approaches.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi all.

I want to make a query. My query works. But when i wanto take max of the
three columns, it gives some errors (works but not with errors).

My query is like this.

http://img399.imageshack.us/img399/9467/myquery1oc.jpg


The column in red box is where i want to calculate and the pink ones, are
the elements of the query. And my function is like;

Kum Program:
IIf(NZ([BAKIYE_FORECAST]![FORECAST_PRG])>(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![Q
T_CUMPROGRAM]));IIf(NZ([BAKIYE_FORECAST]![FORECAST_PRG])>(NZ([830
PROGRAMLARI]![830 KUM PRG]));(NZ([BAKIYE_FORECAST]![FORECAST_PRG]));(NZ([830
PROGRAMLARI]![830 KUM
PRG])));IIf(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![QT_CUMPROGRAM])>(NZ([830
PROGRAMLARI]![830 KUM
PRG]));(NZ([BAKIYE_KUMPRG_SHIP_DATEMAX]![QT_CUMPROGRAM]));(NZ([830
PROGRAMLARI]![830 KUM PRG]))))

When i run the query it gives an error (or lets say an input box, but it’s
an error for me ïŠ)like;

http://img399.imageshack.us/img399/5414/error18qt.jpg

and then this;

http://img283.imageshack.us/img283/9885/error24gy.jpg

And my query works when i click on ok.

But, when i don’t use this column, i mean when i don’t try to get the max of
the columns, columns forecast_prog and kum_prg have non zero items, but when
i start using the functions all of the cells in that two columns are zero.

I wonder why?

And how can i solve this?

Thanx a lot...

 

[OfficeDev18 via AccessMonster.com]

I tried finding the max of 3 columns called a, b, and c using the following
nested IIf() function, and it worked.

IIf(([a]>) And ([a]>[c]),[a],IIf((>[a]) And (>[c]),,[c]))

HTH

Hi Jeff,

First, thanx for replying.

I think i forgot to use “; 0” in NZ, i will change this and try again. But i
think that’s not the source of problem.

I have 3 tables. For the forecast of selling an x product, i have 3 numbers.
And i calculate them in some make table queries. For some products, they can
have 2 forecast, and i must make the null value “0”. And the one you see in
the picture is my final query. So i take 3 columns from that tables and
trying to compare, which one is biggest.

In my final table i must see the three values and the maximum of that values.

If it is easier, i can make this a new table, then will compare that 3
columns.

Thanx..

A couple things to consider...

[quoted text clipped - 59 lines]