My Macro conditions don't work

[Rosana]

This is driving me mad. It has to do with logics but I am
not very good at it, obviously.

I have made a macro that runs as an event of a 'Show'
button on a Dialogue form. It is designed to show
different orders depending on the information you enter on
the dialogue. The possibilities are entering Beginning
Date and/or Ending Date and/or Customer name.

All the conditions I have written work except for 5 and 7
that retrieve nothing. I know they are incompatible with
the rest because they run fine when I delete all the other
conditions.

CAN YOU SEE WHERE IS THE INCOMPATIBILITY?

THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!


1. IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect])

2. Not (IsNull([BeginningDate] And [EndingDate])) And
IsNull([CustomerSelect])

3. Not (IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect]))

4. Not (IsNull([BeginningDate])) And IsNull([EndingDate])
And IsNull([CustomerSelect])

5. Not (IsNull([BeginningDate] And [CustomerSelect])) And
IsNull([EndingDate])

6. Not (IsNull([EndingDate])) And IsNull([BeginningDate])
And IsNull([CustomerSelect])

7. Not (IsNull([EndingDate] And [CustomerSelect])) And
IsNull([BeginningDate])

8. IsNull([BeginningDate] And [EndingDate]) And Not (IsNull
([CustomerSelect]))

 

[Ken Snell [MVP]]

Those two conditions are testing whether the AND of the two controls' values
is Null.

#5:
Not (IsNull([BeginningDate] And [CustomerSelect])) And IsNull([EndingDate])

The above does an AND comparison of BeginningDate and CustomerSelect, and
then tests the result for a Null value. However, the result of an AND
comparison will always be either True (-1) or False (0). Is this what you're
trying to achieve?

 

[Guest]

Well,

I have a form with Beginning Date, Ending Date and
Customer controls. A preview button open another form
filtering the data according to the Beginning/Ending Date
and Customer that the user has entered.

In the Macro that filters that, I have written several
conditions, depending on if the user enter all the
parametres or leave some empty (Null).

For example, they can specify Beginning and End Date but
leave Customer empty.

All conditions work except for 5 and 7. I don't know why.

Rosana

-----Original Message-----
Those two conditions are testing whether the AND of the two controls' values
is Null.

#5:
Not (IsNull([BeginningDate] And [CustomerSelect])) And IsNull([EndingDate])

The above does an AND comparison of BeginningDate and CustomerSelect, and
then tests the result for a Null value. However, the result of an AND
comparison will always be either True (-1) or False (0). Is this what you're
trying to achieve?

--

Ken Snell
<MS ACCESS MVP>

This is driving me mad. It has to do with logics but I am
not very good at it, obviously.

I have made a macro that runs as an event of a 'Show'
button on a Dialogue form. It is designed to show
different orders depending on the information you enter on
the dialogue. The possibilities are entering Beginning
Date and/or Ending Date and/or Customer name.

All the conditions I have written work except for 5 and 7
that retrieve nothing. I know they are incompatible with
the rest because they run fine when I delete all the other
conditions.

CAN YOU SEE WHERE IS THE INCOMPATIBILITY?

THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!


1. IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect])

2. Not (IsNull([BeginningDate] And [EndingDate])) And
IsNull([CustomerSelect])

3. Not (IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect]))

4. Not (IsNull([BeginningDate])) And IsNull ([EndingDate])
And IsNull([CustomerSelect])

5. Not (IsNull([BeginningDate] And [CustomerSelect])) And
IsNull([EndingDate])

6. Not (IsNull([EndingDate])) And IsNull ([BeginningDate])
And IsNull([CustomerSelect])

7. Not (IsNull([EndingDate] And [CustomerSelect])) And
IsNull([BeginningDate])

8. IsNull([BeginningDate] And [EndingDate]) And Not (IsNull
([CustomerSelect]))

.

 

[tina]

well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the comparison of
[BeginningDate] And [CustomerSelect] will always be either True or False, so
it will never return a Null value.
you need to group your evals appropriately. first, the IsNull() function
does not need parentheses directly around it - it is a single function. so
the expression
Not (IsNull([EndingDate]))
should be
Not IsNull([EndingDate])
it doesn't make a difference to the outcome, but unneeded parentheses can be
confusing, and indicates a lack of understanding of their use in grouping
evals.
suggest you re-examine all your expressions. keep in mind that when Access
processes an expression, any part of an expression grouped within
parentheses is reduced to a single value (in these cases, True or False)
first. then that value is used in processing the part(s) of the expression
outside the parentheses. and don't confuse "grouping" parentheses with the
parentheses used by a function such as Null(). function parentheses enclose
the value(s) to be passed to the function for processing.

hth

Well,

I have a form with Beginning Date, Ending Date and
Customer controls. A preview button open another form
filtering the data according to the Beginning/Ending Date
and Customer that the user has entered.

In the Macro that filters that, I have written several
conditions, depending on if the user enter all the
parametres or leave some empty (Null).

For example, they can specify Beginning and End Date but
leave Customer empty.

All conditions work except for 5 and 7. I don't know why.

Rosana

-----Original Message-----
Those two conditions are testing whether the AND of the two controls' values
is Null.

#5:
Not (IsNull([BeginningDate] And [CustomerSelect])) And IsNull([EndingDate])

The above does an AND comparison of BeginningDate and CustomerSelect, and
then tests the result for a Null value. However, the result of an AND
comparison will always be either True (-1) or False (0). Is this what you're
trying to achieve?

--

Ken Snell
<MS ACCESS MVP>

This is driving me mad. It has to do with logics but I am
not very good at it, obviously.

I have made a macro that runs as an event of a 'Show'
button on a Dialogue form. It is designed to show
different orders depending on the information you enter on
the dialogue. The possibilities are entering Beginning
Date and/or Ending Date and/or Customer name.

All the conditions I have written work except for 5 and 7
that retrieve nothing. I know they are incompatible with
the rest because they run fine when I delete all the other
conditions.

CAN YOU SEE WHERE IS THE INCOMPATIBILITY?

THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!


1. IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect])

2. Not (IsNull([BeginningDate] And [EndingDate])) And
IsNull([CustomerSelect])

3. Not (IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect]))

4. Not (IsNull([BeginningDate])) And IsNull ([EndingDate])
And IsNull([CustomerSelect])

5. Not (IsNull([BeginningDate] And [CustomerSelect])) And
IsNull([EndingDate])

6. Not (IsNull([EndingDate])) And IsNull ([BeginningDate])
And IsNull([CustomerSelect])

7. Not (IsNull([EndingDate] And [CustomerSelect])) And
IsNull([BeginningDate])

8. IsNull([BeginningDate] And [EndingDate]) And Not (IsNull
([CustomerSelect]))

.

 

[Ken Snell [MVP]]

well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the comparison of
[BeginningDate] And [CustomerSelect] will always be either True or False, so
it will never return a Null value.

Right on the money, tina!

;-)

 

[Ken Snell [MVP]]

tina has posted a different perspective on what I've said. To repeat, your
condition does not appear to be testing what you think it should be testing,
and because I have no idea what you actually want to test, I cannot suggest
a different way of writing the expression. If you can describe in words what
conditions 5 and 7 are supposed to test and what you want the result to be
for each possible result, we can help you more.

--

Ken Snell
<MS ACCESS MVP>

Well,

I have a form with Beginning Date, Ending Date and
Customer controls. A preview button open another form
filtering the data according to the Beginning/Ending Date
and Customer that the user has entered.

In the Macro that filters that, I have written several
conditions, depending on if the user enter all the
parametres or leave some empty (Null).

For example, they can specify Beginning and End Date but
leave Customer empty.

All conditions work except for 5 and 7. I don't know why.

Rosana

-----Original Message-----
Those two conditions are testing whether the AND of the two controls' values
is Null.

#5:
Not (IsNull([BeginningDate] And [CustomerSelect])) And IsNull([EndingDate])

The above does an AND comparison of BeginningDate and CustomerSelect, and
then tests the result for a Null value. However, the result of an AND
comparison will always be either True (-1) or False (0). Is this what you're
trying to achieve?

--

Ken Snell
<MS ACCESS MVP>

This is driving me mad. It has to do with logics but I am
not very good at it, obviously.

I have made a macro that runs as an event of a 'Show'
button on a Dialogue form. It is designed to show
different orders depending on the information you enter on
the dialogue. The possibilities are entering Beginning
Date and/or Ending Date and/or Customer name.

All the conditions I have written work except for 5 and 7
that retrieve nothing. I know they are incompatible with
the rest because they run fine when I delete all the other
conditions.

CAN YOU SEE WHERE IS THE INCOMPATIBILITY?

THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!


1. IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect])

2. Not (IsNull([BeginningDate] And [EndingDate])) And
IsNull([CustomerSelect])

3. Not (IsNull([BeginningDate] And [EndingDate] And
[CustomerSelect]))

4. Not (IsNull([BeginningDate])) And IsNull ([EndingDate])
And IsNull([CustomerSelect])

5. Not (IsNull([BeginningDate] And [CustomerSelect])) And
IsNull([EndingDate])

6. Not (IsNull([EndingDate])) And IsNull ([BeginningDate])
And IsNull([CustomerSelect])

7. Not (IsNull([EndingDate] And [CustomerSelect])) And
IsNull([BeginningDate])

8. IsNull([BeginningDate] And [EndingDate]) And Not (IsNull
([CustomerSelect]))

.

 

[tina]

oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And [BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i just
saw that in a post a few minutes ago)? i've never run across either example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!

well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the comparison of
[BeginningDate] And [CustomerSelect] will always be either True or

False,

so
it will never return a Null value.

Right on the money, tina!

;-)

 

[Ken Snell [MVP]]

Actually, I must modify slightly what I posted. If Null is AND'd with
anything (Null, True, or False), it'll yield Null as the result, not True or
False. So, I suppose one might use
IsNull(Value1 AND Value2)

as another way of testing:
IsNull(Value1) Or IsNull(Value2)

Can't think of why I might use the first expression instead of the second,
but maybe there might be some reason some day where the first expression
would be useful.
--

Ken Snell
<MS ACCESS MVP>

oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And [BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i just
saw that in a post a few minutes ago)? i've never run across either example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!

well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the comparison of
[BeginningDate] And [CustomerSelect] will always be either True or

False,

so
it will never return a Null value.

Right on the money, tina!

;-)

 

[Steve Schapel]

Tina,

Generally the IsNull() function should not be used in a macro condition,
and similarly a query criteria. The preferred syntax is x Is Null. I
agree 100% with Ken, and to elaborate with your question, it would never
really be applicable to use "IsNull([AField] And [BField])" or
"IsNull([AField] Or [BField])". There would be times where
"IsNull([AField]+[BField])" would be useful, and in fact I frequently
use this syntax in VBA procedures, as a shortcut for "IsNull([AField])
Or IsNull([BField])". In a macro condition or query criteria, if
someone used syntax such as "IsNull([AField] And [BField])" I would
imagine they might really mean "[AField] Is Null And [BField] Is Null".

 

[tina]

hmmm, i thought i had kind of a handle on it, but maybe not. in
IsNull(Value1 AND Value2)
is "And" operating as a concatenator, the way we use an ampersand? that
would mean the combined value would only be Null if both individual values
were Null, right? so if we use "+" instead, as Steve demonstrated in his
post, then the combined value would be Null if *either one* of the
individual values was Null, correct?
am i any closer at all to understanding how this works? <weak grin>

Actually, I must modify slightly what I posted. If Null is AND'd with
anything (Null, True, or False), it'll yield Null as the result, not True or
False. So, I suppose one might use
IsNull(Value1 AND Value2)

as another way of testing:
IsNull(Value1) Or IsNull(Value2)

Can't think of why I might use the first expression instead of the second,
but maybe there might be some reason some day where the first expression
would be useful.
--

Ken Snell
<MS ACCESS MVP>

oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And [BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i just
saw that in a post a few minutes ago)? i've never run across either example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!

well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the

comparison

of

[BeginningDate] And [CustomerSelect] will always be either True or False,
so
it will never return a Null value.

Right on the money, tina!

;-)

 

[tina]

i didn't want to post my responding froth of confusion to both your post and
Ken's, so i just posted to his...if you wouldn't mind taking a look...

btw, i've often wondered about using the IsNull() function in macro
conditions and query criteria. i always used a straight statement "x is
null", as you say, and didn't realize IsNull() would even work in those
spots - until i saw it numerous times here in the newsgroups. but i was
never comfortable with it (though i use IsNull() consistently in VBA, of
course), so have continued using straight statements - and wondering if i
was missing the boat. i'm glad to know which way is considered "best
practice" - thx!

Tina,

Generally the IsNull() function should not be used in a macro condition,
and similarly a query criteria. The preferred syntax is x Is Null. I
agree 100% with Ken, and to elaborate with your question, it would never
really be applicable to use "IsNull([AField] And [BField])" or
"IsNull([AField] Or [BField])". There would be times where
"IsNull([AField]+[BField])" would be useful, and in fact I frequently
use this syntax in VBA procedures, as a shortcut for "IsNull([AField])
Or IsNull([BField])". In a macro condition or query criteria, if
someone used syntax such as "IsNull([AField] And [BField])" I would
imagine they might really mean "[AField] Is Null And [BField] Is Null".

--
Steve Schapel, Microsoft Access MVP

oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And [BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i just
saw that in a post a few minutes ago)? i've never run across either example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!

 

[Ken Snell [MVP]]

AND is an operator that does a boolean / logical comparison between the two
values -- it is not concatentating the values.

You can test out the results of testing Null against True or False using AND
and OR in the Immediate Window of the VBE. It doesn't necessarily give the
results that you might predict:

?Null And Null
Null

?Null And True
Null

?Null And False
False

?Null Or True
True

?Null Or False
Null

?Null Or Null
Null

These results also can be found in Help if you search index for Boolean and
then click on And Operator or on Or Operator in the resulting list of
topics.

--

Ken Snell
<MS ACCESS MVP>

hmmm, i thought i had kind of a handle on it, but maybe not. in
IsNull(Value1 AND Value2)
is "And" operating as a concatenator, the way we use an ampersand? that
would mean the combined value would only be Null if both individual values
were Null, right? so if we use "+" instead, as Steve demonstrated in his
post, then the combined value would be Null if *either one* of the
individual values was Null, correct?
am i any closer at all to understanding how this works? <weak grin>

Actually, I must modify slightly what I posted. If Null is AND'd with
anything (Null, True, or False), it'll yield Null as the result, not

True

or
False. So, I suppose one might use
IsNull(Value1 AND Value2)

as another way of testing:
IsNull(Value1) Or IsNull(Value2)

Can't think of why I might use the first expression instead of the second,
but maybe there might be some reason some day where the first expression
would be useful.
--

Ken Snell
<MS ACCESS MVP>

oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And [BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i just
saw that in a post a few minutes ago)? i've never run across either example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!


well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the

comparison

of
[BeginningDate] And [CustomerSelect] will always be either True or
False,
so
it will never return a Null value.

Right on the money, tina!

;-)

 

[Steve Schapel]

Tina,

Ken's explanation is good. The summary of it, I would say, is just
avoid the use of Logical Operators (e.g. And, Or) in these conditions.
THe reason why the results as shown by Ken are confusing is because it's
sort of like trying to drive a car in water... not really designed for
the purpose. I can't think of a valid usage for IsNull(Value1 AND
Value2). To further clarify the distinction between logical operator
and concatenation that you asked about...

? IsNull(Null AND 73)
True

? IsNull(Null AND "tina")
runtime error: type mismatch

? IsNull(Null+73)
True

? IsNull(Null+"tina")
True

? IsNull(73+"tina")
runtime error: type mismatch

? IsNull("73"+"tina")
False

 

[tina]

i searched on Boolean in A2003 online and offline Help, as Ken suggested,
and came up with numerous topics that, naturally, had nothing to do with
Boolean. (though i'm not remotely surprised; A2003 Help is several clicks
worse than A2000 Help, which i had thought was impossible.)
at any rate, thanks so much for taking the time to try and help me
understand this, guys. i'm afraid it's turned out to be one of those
skull-of-solid-ivory topics for me; so i'll be content (well, not content;
frustrated with myself, but resigned, is more accurate <g>) to simply
sidestep the issue by continuing to use IsNull() with single values only, as
Steve suggested. thanks again, both of you, for your time and patience!

AND is an operator that does a boolean / logical comparison between the two
values -- it is not concatentating the values.

You can test out the results of testing Null against True or False using AND
and OR in the Immediate Window of the VBE. It doesn't necessarily give the
results that you might predict:

?Null And Null
Null

?Null And True
Null

?Null And False
False

?Null Or True
True

?Null Or False
Null

?Null Or Null
Null

These results also can be found in Help if you search index for Boolean and
then click on And Operator or on Or Operator in the resulting list of
topics.

--

Ken Snell
<MS ACCESS MVP>

hmmm, i thought i had kind of a handle on it, but maybe not. in
IsNull(Value1 AND Value2)
is "And" operating as a concatenator, the way we use an ampersand? that
would mean the combined value would only be Null if both individual values
were Null, right? so if we use "+" instead, as Steve demonstrated in his
post, then the combined value would be Null if *either one* of the
individual values was Null, correct?
am i any closer at all to understanding how this works? <weak grin>

Actually, I must modify slightly what I posted. If Null is AND'd with
anything (Null, True, or False), it'll yield Null as the result, not

True

or
False. So, I suppose one might use
IsNull(Value1 AND Value2)

as another way of testing:
IsNull(Value1) Or IsNull(Value2)

Can't think of why I might use the first expression instead of the second,
but maybe there might be some reason some day where the first expression
would be useful.
--

Ken Snell
<MS ACCESS MVP>



oh, good, thx Ken, i wasn't at all sure!
for my own education: is it ever appropriate to use "[AField] And
[BField]"
in an IsNull() function? or "[AField] Or [BField]", for that matter (i
just
saw that in a post a few minutes ago)? i've never run across either
example
before; the evaluation logic is escaping me, so i could use some guidance.
tia!


well, i've never used "And" in an IsNull() function that way. if i
understood Ken's explanation right, the expression
(IsNull([BeginningDate] And [CustomerSelect]))
will *always* evaluate to False (is not Null), because the comparison
of
[BeginningDate] And [CustomerSelect] will always be either True or
False,
so
it will never return a Null value.

Right on the money, tina!

;-)