Help to create a querie / Great Challange

[Marco]

Hello. I need to create a querie that analise a a specific value and compare
to some standards. Well, I was able to create this querie if the were no such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this 1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that my
querie analizes only the Min and forget the UMin. I even tought to say: " if
it 0 then that stand would be something like -1000 or 1000. but for some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is not to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Douglas J. Steele]

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change, so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max], [UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

 

[Marco]

Hi Douglas,

Thanks. I'm going to try. so, you think that if I use NZ before each
standard value I be able to solve my problem?

I'll try and i'll give you my feedback.

Thanks,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change, so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max], [UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this 1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that my
querie analizes only the Min and forget the UMin. I even tought to say: "
if
it 0 then that stand would be something like -1000 or 1000. but for some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max], [UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change, so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max], [UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this 1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that my
querie analizes only the Min and forget the UMin. I even tought to say: "
if
it 0 then that stand would be something like -1000 or 1000. but for some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Douglas J. Steele]

Something appears to have gone amiss posting, plus you fell victim of word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that
my
querie analizes only the Min and forget the UMin. I even tought to say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hi.

Is not reurning an error now. But I need your help because the querie is not
compating to well the values.

I'm using this querie:
teste1:IIf([NewThickRectas] < Nz([CompactacaoRectasUMin]; [NewThickRectas])
Or [NewThickRectas] > Nz([CompactacaoRectasUMax]; [NewThickRectas]); "*" &
[NewThickRectas] & "*"; IIf([NewThickRectas] < nz([CompactacaoRectasMin];
[NewThickRectas]) Or [NewThickRectas] > Nz([CompactacaoRectasMax];
[NewThickRectas]); "*" & [NewThickRectas]; [NewThickRectas]))


This are my standards:

Umin: (is null)
Min: 1.30
Nom: 1.40
Max: 1.50
Umax: ( is null)

My value to compare is: (the field name is [NewThickRectas] )

The values that has to be compared are:
1.47
1.53
1.56
1.45

The result should be this:
1.47
*1.53
*1.56
1.45

but instead appears this:
*1.47
*1.53
*1.56
*1.45

Something appears to have gone amiss posting, plus you fell victim of word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that
my
querie analizes only the Min and forget the UMin. I even tought to say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hi. I thinl I found what my problem was. AND LET ME TELL YOU THAT YOU ARE
GREAT. IS WORKING PERFECTLY.

THANKS.
Marco

Something appears to have gone amiss posting, plus you fell victim of word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that
my
querie analizes only the Min and forget the UMin. I even tought to say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco

Something appears to have gone amiss posting, plus you fell victim of word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco

Presumably if one of the fields has no value, it actually contains Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want that
my
querie analizes only the Min and forget the UMin. I even tought to say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max], [MyValue])),
_
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax
Or
Min
or Max. In this cases I can't use zero, because is a number and the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Thanks man.

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max], [MyValue])),
_
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax
Or
Min
or Max. In this cases I can't use zero, because is a number and the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hello Douglas.

I'm with another problem. I've no one else to go besides you, i'm sorry.
There's another problem, you querie is perfect but Crystal 6 don't like NZ
funtion.

Everything was working fine in my queries but when I tried to insert the
queries in report Crystyal Crystal says to me that there's erros in NZ
functions. So now I have to solve my problem in other way. There's no null to
compare only zeros and I cannot use NZ function.

Do you have any solution?

Regards,
Marco

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max], [MyValue])),
_
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or Umax
Or
Min
or Max. In this cases I can't use zero, because is a number and the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[John Spencer]

Try using only IIF and see if that works

IIf([MyValue] < IIf([UMin] Is Not Null, UMin, [MyValue]) = 0, [MyValue],
IIF([UMin] Is Not Null, Umin,[MyValue])) OR

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

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])), _
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Douglas J. Steele]

I don't see how that will solve the issue with UMin being 0, John.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try using only IIF and see if that works

IIf([MyValue] < IIf([UMin] Is Not Null, UMin, [MyValue]) = 0, [MyValue],
IIF([UMin] Is Not Null, Umin,[MyValue])) OR

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

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])), _
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco




:

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong
number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue]

Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value
should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the
UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like
this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax Or
Min
or Max. In this cases I can't use zero, because is a number and
the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but
for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Douglas J. Steele]

See whether you can replace all of the Nz(x, y) with IIf(Isnull(x), y, x) or
IIf(x IS NULL, y, x)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas.

I'm with another problem. I've no one else to go besides you, i'm sorry.
There's another problem, you querie is perfect but Crystal 6 don't like NZ
funtion.

Everything was working fine in my queries but when I tried to insert the
queries in report Crystyal Crystal says to me that there's erros in NZ
functions. So now I have to solve my problem in other way. There's no null
to
compare only zeros and I cannot use NZ function.

Do you have any solution?

Regards,
Marco

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])),
_
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco




:

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong
number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue]

Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value
should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the
UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like
this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax
Or
Min
or Max. In this cases I can't use zero, because is a number and
the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I
want
that
my
querie analizes only the Min and forget the UMin. I even tought
to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but
for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means
that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

hello Douglas. Thanks once again for your support.

This Is the querie that I create after you last post:


IIf([NewThickCantos]<iif(isnull([NewThickUMin]/1000);[NewThickCantos];([NewThickUMin]/1000))
Or
[NewThickCantos]>iif(isnull([NewThickUMax]/1000);[NewThickCantos];([NewThickUMax]/1000));"*"
& [NewThickCantos] &
"*";IIf([NewThickCantos]<iif(([NewThickMin]/1000);[NewThickCantos];
([NewThickMin]/1000)) Or
[NewThickCantos]>iif(([NewThickMax]/1000);[NewThickCantos];([NewThickMin]/1000));"*"
&
[NewThickCantos];IIf([NewThickCantos]>=(iif(isnull([NewThickMin]/1000);[NewThickCantos];([NewThickMin]/1000)))
Or
[NewThickCantos]<=(iif(isnull([NewThickMax]/1000);[NewThickCantos];([NewThickMax]/1000)));[NewThickCantos])))


This querie don't retrieve an error but is not comparing the results very
well.

There's a MAX standard value and one of the results should be *1.71

Here it is my standards and my values:

UMin: Null ( I can change to appear zero)
Min: 1.40
Nom: 1.55
Max: 1.65
UMax: Null (but i can make appear zero)

This are the values:

1.71
1.58
1.55
1.59

The result should be:
*1.71
1.58
1.55
1.59


Please tell me if you have any more idea.

Thanks,
Marco

See whether you can replace all of the Nz(x, y) with IIf(Isnull(x), y, x) or
IIf(x IS NULL, y, x)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas.

I'm with another problem. I've no one else to go besides you, i'm sorry.
There's another problem, you querie is perfect but Crystal 6 don't like NZ
funtion.

Everything was working fine in my queries but when I tried to insert the
queries in report Crystyal Crystal says to me that there's erros in NZ
functions. So now I have to solve my problem in other way. There's no null
to
compare only zeros and I cannot use NZ function.

Do you have any solution?

Regards,
Marco

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])),
_
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco




:

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong
number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue]

Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value
should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the
UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like
this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax
Or
Min
or Max. In this cases I can't use zero, because is a number and
the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I
want
that
my
querie analizes only the Min and forget the UMin. I even tought
to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but
for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means
that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Hello, I think I found my mistake. Please shoot me!

Let's see if now I'll solve my problems.

regards,
Marco

I don't see how that will solve the issue with UMin being 0, John.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try using only IIF and see if that works

IIf([MyValue] < IIf([UMin] Is Not Null, UMin, [MyValue]) = 0, [MyValue],
IIF([UMin] Is Not Null, Umin,[MyValue])) OR

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

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])), _
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco




:

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong
number of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue]

Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value
should be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the
UMax and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like
this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax Or
Min
or Max. In this cases I can't use zero, because is a number and
the SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but
for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco

 

[Marco]

Thanks John for your support. I'm really stuck here. let's see if I'm able
now to go foward.

regards,
Marco

Try using only IIF and see if that works

IIf([MyValue] < IIf([UMin] Is Not Null, UMin, [MyValue]) = 0, [MyValue],
IIF([UMin] Is Not Null, Umin,[MyValue])) OR

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

Untested, but try:

IIf([MyValue] < IIf(Nz([UMin], [MyValue]) = 0, [MyValue], Nz([UMin],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [UMyValue]) = 0, [UMyValue], Nz([Max],
[UMyValue])), _
"*" & [MyValue] & "*", _
IIf([MyValue] < IIf(Nz([Min], [MyValue]) = 0, [MyValue], Nz([Min],
[MyValue])) Or _
[MyValue] > IIf(Nz([Max], [MyValue]) = 0, [MyValue], Nz([Max],
[MyValue])), _
"*" & [MyValue], _
[MyValue]))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas. I need another favor from you.

Remember this querie that you provid me?

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))


Now I have another problem. The fields now are not nulls Now they have
zeros
on it. But the zeros want to mean that they are null.

I need that when the querie find zeros treat it as nulls.

Is it possible?

Marco




:

Something appears to have gone amiss posting, plus you fell victim of
word
wrap (the suggestion was supposed to be all on one line)

With line continuation characters, it's

IIf([MyValue] < Nz([UMin], [MyValue]) Or _
[MyValue] > Nz([Max], [UMyValue]), _
"*" & [MyValue] & "*", _
IIf([MyValue] < Nz([Min], [MyValue]) Or _
[MyValue] > Nz([Max], [MyValue]), _
"*" & [MyValue], _
[MyValue]))



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Douglas.

I tried you querie but it returns me the folowing error: "Wrong number
of
arguments used with function in query expression."


By the way. I was here thinking about and in the first IIF condition
you
want to really mean Max or UMax?

"IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]), "

And what's UMyValue ? You want to mean MyValue?


Please reply to me. I need your help.

Regards,
Marco


















:

Presumably if one of the fields has no value, it actually contains
Null.
That means you can use the Nz function to change the value to
whatever
you
want. For instance, you could use

Nz([UMax], [MyValue])

and then UMax would be the same value as the one you're trying to
change,
so
that it wouldn't impact your comparisons.

That means something like the following untested air code should do
what
you
want:

IIf([MyValue] < Nz([UMin], [MyValue] Or [MyValue] > Nz([Max],
[UMyValue]),
"*" & [MyValue] & "*", [MyValue] < Nz([Min], [MyValue] Or [MyValue] >
Nz([Max], [MyValue]), "*" & [MyValue], [MyValue]))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello. I need to create a querie that analise a a specific value
and
compare
to some standards. Well, I was able to create this querie if the
were
no
such
specifications.

Imagine that I have a value, i.e.: 1.50

My standards columns are:
UMin; Min; Nom; Max; UMax.

If the value if less then UMIn or bigger then Umax the value should
be
represented like this *1.50*.

If the value is bigger then UMin and less then Min or Less the UMax
and
bigger the Max should be represented like this *1.50.

If the value is between Max and Min should be represented like this
1.50.

Now it comes the problem. Something there's no value on Umin or
Umax Or
Min
or Max. In this cases I can't use zero, because is a number and the
SQL
querie would think that should compare with it.

What I wanted is, if one of these standards were zero, the querie
wouldn't
count with it.

Imagine that I have a standard value on Min but not on Umin I want
that
my
querie analizes only the Min and forget the UMin. I even tought to
say:
"
if
it 0 then that stand would be something like -1000 or 1000. but for
some
reason I may have standards on Umax but not on Max.

Imagine this case: Value to compare: 1.30

UMin Min Nom Max UMax
0.90 1.00 1.10 1.20 0

In this case the result should be *1.30 » In this case, my value
is
bigger
then 1.20 and bigger then Umax. But as UMax is zero, it means that
is
not
to
be compared with that standard.

Please help me.

Regards in avance,
Marco