Subject: Should I use "IsNull" or something else? (Duh!)

J

Jim In Minneapolis

Hi. I'm pretty new at this -- can someone help me out?

Here's my VBA module argument:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) Then

It works great, but I need to add a condition that says: "If the text field
[Midpoint Statement] in this same record is *NOT EMPTY* (in other words, if
the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument (just skip the entire argument -- pretend it isn't even there! --
and go on to the next argument in the module).

I've been playing around with "Is Null" but I keep getting error messages --
I'm afraid I don't know the syntax.

If anyone knows how to fix this, I'll sure be grateful.

***IMPORTANT!: Please show me the 'final product' -- send me the COMPLETE
'fixed' argument line, ok? Don't just tell me to 'insert' something --
'cause I'll surely screw it up! :(

Thanks for your patience.

Jim
 
D

Duane Hookom

Try something like:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) AND Not IsNull(.[Midpoint Statement]) Then
 
J

Jim In Minneapolis

Thank you, Duane

You've been very helpful to me . . . I appreciate your taking the time for me.

Jim

Duane Hookom said:
Try something like:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) AND Not IsNull(.[Midpoint Statement]) Then


--
Duane Hookom In Eau Claire
MS Access MVP


Jim In Minneapolis said:
Hi. I'm pretty new at this -- can someone help me out?

Here's my VBA module argument:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected
Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) Then

It works great, but I need to add a condition that says: "If the text
field
[Midpoint Statement] in this same record is *NOT EMPTY* (in other words,
if
the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument (just skip the entire argument -- pretend it isn't even there! --
and go on to the next argument in the module).

I've been playing around with "Is Null" but I keep getting error
messages --
I'm afraid I don't know the syntax.

If anyone knows how to fix this, I'll sure be grateful.

***IMPORTANT!: Please show me the 'final product' -- send me the COMPLETE
'fixed' argument line, ok? Don't just tell me to 'insert' something --
'cause I'll surely screw it up! :(

Thanks for your patience.

Jim
Click to expand...
Click to expand...
 
M

matt -`;'-

Jim In Minneapolis said:
Hi. I'm pretty new at this -- can someone help me out?

Here's my VBA module argument:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) Then

It works great, but I need to add a condition that says: "If the text field
[Midpoint Statement] in this same record is *NOT EMPTY* (in other words, if
the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument (just skip the entire argument -- pretend it isn't even there! --
and go on to the next argument in the module).

I've been playing around with "Is Null" but I keep getting error messages --
I'm afraid I don't know the syntax.

If anyone knows how to fix this, I'll sure be grateful.

***IMPORTANT!: Please show me the 'final product' -- send me the COMPLETE
'fixed' argument line, ok? Don't just tell me to 'insert' something --
'cause I'll surely screw it up! :(

Thanks for your patience.

Jim
Click to expand...

Hi Jim,

An easier to follow code style could be achieved by using a flag.
(Flag: a variable that is set when a condition is true.)
Dim PassCondition as Integer 'The flag
PassCondition=0

If (.[Planet Number].Column(1)="Moon" Or .[Planet Number].Column(1)="Saturn") Then
PassCondition = PassCondition + 1
End If 'Test 1 add 1 on pass

If (.[Aspect Number].Column(1)="Conjunct") Then
PassCondition = PassCondition + 1
End If 'Test 2 add 1 on pass

If (.[Aspect Planet Number].Column(1)="Moon" Or .[Aspected Planet Number].Column(1) = "Saturn") Then
PassCondition = PassCondition + 1
End If 'Test 3 add 1 on pass

If (Len(.[Midpoint Statement].Column(1)) > 0) Then
PassCondition = 0
End If

If (PassCondition = 3) Then
'Do whatever you had planned when all conditions are met.
End If

I think it is easier to see how the logic works when it is not condensed - maybe that's just me.

The first three tests increment the PassCondition variable if they meet a pass condition.

The last test allows a pass condition only if there is no text in the [Midpoint Statement].

Based on your goal:
if the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument
Click to expand...

I used the Len() function to test the length of the string. You could also test for an empty string by using 2 quote symbols, no
space between them. example: If (str = "") Then

I like using flags on occasion in VBA code because it helps keep it clear, or sometimes because of timing and location in a loop.

-matt
 
J

Jim In Minneapolis

Thanks, Matt

You've given me some excellent advice! I appreciate your taking the time.

God bless

Jim

matt -`;'- said:
Jim In Minneapolis said:
Hi. I'm pretty new at this -- can someone help me out?

Here's my VBA module argument:

If ((.[Planet Number].Column(1) = "Moon" Or .[Planet Number].Column(1) =
"Saturn") And .[Aspect Number].Column(1) = "Conjunct" And (.[Aspected Planet
Number].Column(1) = "Moon" Or .[Aspected Planet Number].Column(1) =
"Saturn")) Then

It works great, but I need to add a condition that says: "If the text field
[Midpoint Statement] in this same record is *NOT EMPTY* (in other words, if
the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument (just skip the entire argument -- pretend it isn't even there! --
and go on to the next argument in the module).

I've been playing around with "Is Null" but I keep getting error messages --
I'm afraid I don't know the syntax.

If anyone knows how to fix this, I'll sure be grateful.

***IMPORTANT!: Please show me the 'final product' -- send me the COMPLETE
'fixed' argument line, ok? Don't just tell me to 'insert' something --
'cause I'll surely screw it up! :(

Thanks for your patience.

Jim
Click to expand...

Hi Jim,

An easier to follow code style could be achieved by using a flag.
(Flag: a variable that is set when a condition is true.)
Dim PassCondition as Integer 'The flag
PassCondition=0

If (.[Planet Number].Column(1)="Moon" Or .[Planet Number].Column(1)="Saturn") Then
PassCondition = PassCondition + 1
End If 'Test 1 add 1 on pass

If (.[Aspect Number].Column(1)="Conjunct") Then
PassCondition = PassCondition + 1
End If 'Test 2 add 1 on pass

If (.[Aspect Planet Number].Column(1)="Moon" Or .[Aspected Planet Number].Column(1) = "Saturn") Then
PassCondition = PassCondition + 1
End If 'Test 3 add 1 on pass

If (Len(.[Midpoint Statement].Column(1)) > 0) Then
PassCondition = 0
End If

If (PassCondition = 3) Then
'Do whatever you had planned when all conditions are met.
End If

I think it is easier to see how the logic works when it is not condensed - maybe that's just me.

The first three tests increment the PassCondition variable if they meet a pass condition.

The last test allows a pass condition only if there is no text in the [Midpoint Statement].

Based on your goal:
if the [Midpoint Statement] field has ANYTHING in it, then IGNORE this entire
argument
Click to expand...

I used the Len() function to test the length of the string. You could also test for an empty string by using 2 quote symbols, no
space between them. example: If (str = "") Then

I like using flags on occasion in VBA code because it helps keep it clear, or sometimes because of timing and location in a loop.

-matt
Click to expand...
 
M

matt -`;'-

Jim In Minneapolis said:
Thanks, Matt

You've given me some excellent advice! I appreciate your taking the time.

God bless

Jim
Click to expand...

Your welcome Jim! Sounds like you have a nice project going there.
-matt
 
Top