greater than / less than

[JohnLute]

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[scubadiver]

Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

 

[Michel Walsh]

"AB" goes before "B", and also
"19" goes before "2", in the same way

"10" < "9" and
"10.5" < "9.94"



So, it may be that your fields have a string representation of a number, a
FORMATted number, and NOT a number. As number, it is evident that

10.5 > 9.94

(but as string, that is the reverse).




Vanderghast, Access MVP

 

[JohnLute]

Hi, scubadiver.

Yes - that's more simple however the problem remains. This is odd because
where [CSH] is null it properly returns the value in [UNH] however wherever
[CSH] is 10.5 it returns the less than value in [UNH].

Maybe I'm getting myself spun around. Here's what I'm trying to do in Enlgish:

If [CSH] is greater than [UNH] then return [CSH]
If [CSH] is less than [UNH] then return [UNH]

???

--
www.Marzetti.com

Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[scubadiver]

Are your fields numerical or text string. I have tried your IIF statement
and it seems to work fine for me and my fields are numerical.

Hi, scubadiver.

Yes - that's more simple however the problem remains. This is odd because
where [CSH] is null it properly returns the value in [UNH] however wherever
[CSH] is 10.5 it returns the less than value in [UNH].

Maybe I'm getting myself spun around. Here's what I'm trying to do in Enlgish:

If [CSH] is greater than [UNH] then return [CSH]
If [CSH] is less than [UNH] then return [UNH]

???

--
www.Marzetti.com

Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[JohnLute]

Text string. [CSH] is CSH: Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep]))))
in an underlying query.

--
www.Marzetti.com

Are your fields numerical or text string. I have tried your IIF statement
and it seems to work fine for me and my fields are numerical.

Hi, scubadiver.

Yes - that's more simple however the problem remains. This is odd because
where [CSH] is null it properly returns the value in [UNH] however wherever
[CSH] is 10.5 it returns the less than value in [UNH].

Maybe I'm getting myself spun around. Here's what I'm trying to do in Enlgish:

If [CSH] is greater than [UNH] then return [CSH]
If [CSH] is less than [UNH] then return [UNH]

???

--
www.Marzetti.com

Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

:

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[JohnLute]

That's correct, Michael. As I noted to scubadiver above [CSH] is CSH:
Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep])))) in an underlying query.

Any way around this? I can't flip the > to < because that will reverse the
problem.

Yikes!

--
www.Marzetti.com

"AB" goes before "B", and also
"19" goes before "2", in the same way

"10" < "9" and
"10.5" < "9.94"



So, it may be that your fields have a string representation of a number, a
FORMATted number, and NOT a number. As number, it is evident that

10.5 > 9.94

(but as string, that is the reverse).




Vanderghast, Access MVP

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[scubadiver]

As michael has explained, numbers as text are treated differently. Are you
treating the numbers as text in the underlying query? If 'yes', why?

Text string. [CSH] is CSH: Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep]))))
in an underlying query.

--
www.Marzetti.com

Are your fields numerical or text string. I have tried your IIF statement
and it seems to work fine for me and my fields are numerical.

Hi, scubadiver.

Yes - that's more simple however the problem remains. This is odd because
where [CSH] is null it properly returns the value in [UNH] however wherever
[CSH] is 10.5 it returns the less than value in [UNH].

Maybe I'm getting myself spun around. Here's what I'm trying to do in Enlgish:

If [CSH] is greater than [UNH] then return [CSH]
If [CSH] is less than [UNH] then return [UNH]

???

--
www.Marzetti.com


:


Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

:

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[Michel Walsh]

val(text1) > val(text2)


would compare the number represented by text1 to the one represented by
text2. Note that val works even if there are letters:

? val("12.34WQz456")
12.34




Vanderghast, Access MVP

That's correct, Michael. As I noted to scubadiver above [CSH] is CSH:
Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep])))) in an underlying query.

Any way around this? I can't flip the > to < because that will reverse the
problem.

Yikes!

--
www.Marzetti.com

"AB" goes before "B", and also
"19" goes before "2", in the same way

"10" < "9" and
"10.5" < "9.94"



So, it may be that your fields have a string representation of a number,
a
FORMATted number, and NOT a number. As number, it is evident that

10.5 > 9.94

(but as string, that is the reverse).




Vanderghast, Access MVP

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc
of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't
then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[JohnLute]

Well, I went back and changed to:
CSH: IIf(Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep])))) Is
Null,Null,CCur(Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep]))))))

and then formatted to Fixed with 4 decimal places. I did that with all of
the involved aliases and that seems to have done the trick.

Previously, CSH: Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep])))) wouldn't
permit me to format to Fixed/4 decimals - obviously (or maybe not so
obviously) which rendered them as text.

Thanks for all your help! Maybe you see an easier solution? I tend to have
very complicated ones that someone always seems to be able to shave.

--
www.Marzetti.com

As michael has explained, numbers as text are treated differently. Are you
treating the numbers as text in the underlying query? If 'yes', why?

Text string. [CSH] is CSH: Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep]))))
in an underlying query.

--
www.Marzetti.com

Are your fields numerical or text string. I have tried your IIF statement
and it seems to work fine for me and my fields are numerical.

:

Hi, scubadiver.

Yes - that's more simple however the problem remains. This is odd because
where [CSH] is null it properly returns the value in [UNH] however wherever
[CSH] is 10.5 it returns the less than value in [UNH].

Maybe I'm getting myself spun around. Here's what I'm trying to do in Enlgish:

If [CSH] is greater than [UNH] then return [CSH]
If [CSH] is less than [UNH] then return [UNH]

???

--
www.Marzetti.com


:


Shouldn't it be just this?

CSHc: IIf([CSH]>[UNH],[CSH],[UNH])

Why include the extra IIF?

:

CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[JohnLute]

UGH! Note my reply to scubadiver above regarding simpler solutions!

What is your opinion? Which is the "better" solution - using val or the one
that I did as noted above?

--
www.Marzetti.com

val(text1) > val(text2)


would compare the number represented by text1 to the one represented by
text2. Note that val works even if there are letters:

? val("12.34WQz456")
12.34




Vanderghast, Access MVP

That's correct, Michael. As I noted to scubadiver above [CSH] is CSH:
Nz([CGODH],Nz([CNDep],Nz([PKMSOEH],Nz([THDep])))) in an underlying query.

Any way around this? I can't flip the > to < because that will reverse the
problem.

Yikes!

--
www.Marzetti.com

"AB" goes before "B", and also
"19" goes before "2", in the same way

"10" < "9" and
"10.5" < "9.94"



So, it may be that your fields have a string representation of a number,
a
FORMATted number, and NOT a number. As number, it is evident that

10.5 > 9.94

(but as string, that is the reverse).




Vanderghast, Access MVP



CSHc: IIf([CSH]>[UNH],[CSH],IIf([CSH]<[UNH],[UNH]))

This isn't consistently returning what's expected. For example, I have
records with a UNH of 9.94 and a CSH of 10.5 which should return a CSHc
of
10.5. Instead it's returning 9.94.

Typically, the UNH will always be less than the CSH but when it isn't
then
CSHc will "correct" that and display the greater of the two.

Is there something wrong with my code? Why does it work for most of the
records and for others not?

Thanks in advance!

 

[John Spencer]

Are your fields text fields with number characters?

"9.94" is greater than "10.5"
9.94 is less than 10.5

Check your field types.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County