Custom Text Fields and Formulas

[Brad]

Hi,

I am trying to create a formula (for a custom text field) that creates a
value based on two wildcards in the [Name] field. I have been able to do the
first test:

IIf([Name] Like '*conference*','1')
- I succesfully get a "1" whenever it finds the word 'confernce' in the
[Name] field.

What I need to do now is make an AND statement somethng like:

IIf([Name] Like '*conference*' AND [Name] IS NOT LIKE '*XYZ*],'1')

Any assistance would be greatly appreciated!! I'm stumped.

Thanks,
Brad

 

[JulieS]

Hi Brad,

You may nest IIf functions inside of one another. The following formula
displays "Yes" if the field contains "conference" but doesn't contain "XYZ".
It displays "No" if the field does not contain "conference" but does contain
"XYZ" and displays "Both" if the field contains both "conference" and "XYZ".

IIf([Name] Like "*conference*",IIf([Name] Not Like *XYZ*","Yes","Both"),"No")

Hope this helps. Let us know how you get along.

Julie

 

[Brad]

Thats awesome Julie!!! Many thanks, it worked like a charm.

Regards,
Brad

Hi Brad,

You may nest IIf functions inside of one another. The following formula
displays "Yes" if the field contains "conference" but doesn't contain "XYZ".
It displays "No" if the field does not contain "conference" but does contain
"XYZ" and displays "Both" if the field contains both "conference" and "XYZ".

IIf([Name] Like "*conference*",IIf([Name] Not Like *XYZ*","Yes","Both"),"No")

Hope this helps. Let us know how you get along.

Julie

Hi,

I am trying to create a formula (for a custom text field) that creates a
value based on two wildcards in the [Name] field. I have been able to do the
first test:

IIf([Name] Like '*conference*','1')
- I succesfully get a "1" whenever it finds the word 'confernce' in the
[Name] field.

What I need to do now is make an AND statement somethng like:

IIf([Name] Like '*conference*' AND [Name] IS NOT LIKE '*XYZ*],'1')

Any assistance would be greatly appreciated!! I'm stumped.

Thanks,
Brad

 

[JackD]

You can also use the AND operator.
I suspect that if it doesn't work it is because of a syntax error.

IIf([Flag1] And [Flag2],"A","B") works

So anything like this:

Iif ( (condition 1) AND (condition 2) , "Whatever you want to say if true",
"Whatever you want to say if false")

will work.

Try adding parentheses around your statements where you are using AND, and
don't forget the value if your criteria is false.


--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

Hi Brad,

You may nest IIf functions inside of one another. The following formula
displays "Yes" if the field contains "conference" but doesn't contain "XYZ".
It displays "No" if the field does not contain "conference" but does contain
"XYZ" and displays "Both" if the field contains both "conference" and "XYZ".

IIf([Name] Like "*conference*",IIf([Name] Not Like *XYZ*","Yes","Both"),"No")

Hope this helps. Let us know how you get along.

Julie

Hi,

I am trying to create a formula (for a custom text field) that creates a
value based on two wildcards in the [Name] field. I have been able to do the
first test:

IIf([Name] Like '*conference*','1')
- I succesfully get a "1" whenever it finds the word 'confernce' in the
[Name] field.

What I need to do now is make an AND statement somethng like:

IIf([Name] Like '*conference*' AND [Name] IS NOT LIKE '*XYZ*],'1')

Any assistance would be greatly appreciated!! I'm stumped.

Thanks,
Brad

 

[JulieS]

Hi Brad,

Glad to know it worked. Thanks very much for the feedback. Let us know if
we can assist you again in the future.

Julie

 

[JulieS]

Hi Jack,

Brilliant! So much more elegant than my solution. ;-)

Julie