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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim In Minneapolis - 31 Aug 2005 18:20 GMT

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

Reply to this Message

Duane Hookom - 31 Aug 2005 18:43 GMT

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

Signature

Duane Hookom In Eau Claire
MS Access MVP

> Hi. I'm pretty new at this -- can someone help me out?
>
[quoted text clipped - 27 lines]
>
> Jim

Reply to this Message

Jim In Minneapolis - 01 Sep 2005 07:57 GMT

Thank you, Duane

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

Jim

> Try something like:
>
[quoted text clipped - 34 lines]
> >
> > Jim

Reply to this Message

matt -`;'- - 09 Sep 2005 07:25 GMT

> Hi. I'm pretty new at this -- can someone help me out?
>
[quoted text clipped - 23 lines]
>
> Jim

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

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

Reply to this Message

Jim In Minneapolis - 09 Sep 2005 07:36 GMT

Thanks, Matt

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

God bless

Jim

> > Hi. I'm pretty new at this -- can someone help me out?
> >
[quoted text clipped - 67 lines]
>
> -matt

Reply to this Message

matt -`;'- - 11 Sep 2005 02:06 GMT

> Thanks, Matt
>
[quoted text clipped - 3 lines]
>
> Jim

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

Reply to this Message