IIF with AND and OR operator along with LIKE condition

  • Thread starter Thread starter mcduff22
  • Start date Start date
M

mcduff22

On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like "*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not looking at
the wild card text conditions.

Thanks!
 
I don't really know why your IIF is returning just "_", but you should avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only evaluating
[ExpemptionType] with a value of S & L in your name field. You need to put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks for the reply... Yours was one of the combinations of attempts that I
had tried previously. But, I did paste your IIF below and I am still only
returning the "_" for all records regardless of wild card text content
criteria being met or not met. This one is really stumping me and holding me
up! Thank you for the avoid field "Name" tip. I will keep in mind for
future databases.

Roger Carlson said:
I don't really know why your IIF is returning just "_", but you should avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only evaluating
[ExpemptionType] with a value of S & L in your name field. You need to put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



mcduff22 said:
On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not looking
at
the wild card text conditions.

Thanks!
Click to expand...
Click to expand...
 
Then there's something going on outside of your IIF statement. "Name" might
actually be tripping it up, or it could be something else. You need to look
farther afield.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


mcduff22 said:
Thanks for the reply... Yours was one of the combinations of attempts
that I
had tried previously. But, I did paste your IIF below and I am still only
returning the "_" for all records regardless of wild card text content
criteria being met or not met. This one is really stumping me and holding
me
up! Thank you for the avoid field "Name" tip. I will keep in mind for
future databases.

Roger Carlson said:
I don't really know why your IIF is returning just "_", but you should
avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only
evaluating
[ExpemptionType] with a value of S & L in your name field. You need to
put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



mcduff22 said:
On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the
text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not
looking
at
the wild card text conditions.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
In my testing, if the field is named "Name", it always returning the FALSE
value. If I change the field name to "XName", the IIF() function work
properly.


Besides being a reserved word, "Name" is not very descriptive..... "Name" of
what??? Your pet, a model of car, a tree????


Something like "txtBankName" or "LenderName" is much better. Notice that I
did not use spaces. Those will also give you headaches. If you must seperate
words, use the underscore: "txt_Bank_Name" or "Lender_Name".

Here is a link to a list of reserved words:

http://allenbrowne.com/AppIssueBadWord.html


Also, "The Ten Commandments of Access"

http://mvps.org/access/tencommandments.htm


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Roger Carlson said:
Then there's something going on outside of your IIF statement. "Name" might
actually be tripping it up, or it could be something else. You need to look
farther afield.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


mcduff22 said:
Thanks for the reply... Yours was one of the combinations of attempts
that I
had tried previously. But, I did paste your IIF below and I am still only
returning the "_" for all records regardless of wild card text content
criteria being met or not met. This one is really stumping me and holding
me
up! Thank you for the avoid field "Name" tip. I will keep in mind for
future databases.

Roger Carlson said:
I don't really know why your IIF is returning just "_", but you should
avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only
evaluating
[ExpemptionType] with a value of S & L in your name field. You need to
put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the
text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not
looking
at
the wild card text conditions.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Bingo! It was the "Name" field. Admittedly, I was lazy when I created the
field. To work around it, I added another column in my query named CoName
that pulled the content from the Name field. Then I drove my code from the
report off of the CoName field. It works perfect. Sloppy work around; but,
I'm too far into this database to go back and remame it at the table. Much
appreciation to all that were able to assist!

Steve Sanford said:
In my testing, if the field is named "Name", it always returning the FALSE
value. If I change the field name to "XName", the IIF() function work
properly.


Besides being a reserved word, "Name" is not very descriptive..... "Name" of
what??? Your pet, a model of car, a tree????


Something like "txtBankName" or "LenderName" is much better. Notice that I
did not use spaces. Those will also give you headaches. If you must seperate
words, use the underscore: "txt_Bank_Name" or "Lender_Name".

Here is a link to a list of reserved words:

http://allenbrowne.com/AppIssueBadWord.html


Also, "The Ten Commandments of Access"

http://mvps.org/access/tencommandments.htm


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Roger Carlson said:
Then there's something going on outside of your IIF statement. "Name" might
actually be tripping it up, or it could be something else. You need to look
farther afield.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


mcduff22 said:
Thanks for the reply... Yours was one of the combinations of attempts
that I
had tried previously. But, I did paste your IIF below and I am still only
returning the "_" for all records regardless of wild card text content
criteria being met or not met. This one is really stumping me and holding
me
up! Thank you for the avoid field "Name" tip. I will keep in mind for
future databases.

:

I don't really know why your IIF is returning just "_", but you should
avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only
evaluating
[ExpemptionType] with a value of S & L in your name field. You need to
put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the
text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not
looking
at
the wild card text conditions.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You could probably fix this using

[TableName].[Name] Like ...

In other words fully specify the table and field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Bingo! It was the "Name" field. Admittedly, I was lazy when I created the
field. To work around it, I added another column in my query named CoName
that pulled the content from the Name field. Then I drove my code from the
report off of the CoName field. It works perfect. Sloppy work around; but,
I'm too far into this database to go back and remame it at the table. Much
appreciation to all that were able to assist!

Steve Sanford said:
In my testing, if the field is named "Name", it always returning the FALSE
value. If I change the field name to "XName", the IIF() function work
properly.


Besides being a reserved word, "Name" is not very descriptive..... "Name" of
what??? Your pet, a model of car, a tree????


Something like "txtBankName" or "LenderName" is much better. Notice that I
did not use spaces. Those will also give you headaches. If you must seperate
words, use the underscore: "txt_Bank_Name" or "Lender_Name".

Here is a link to a list of reserved words:

http://allenbrowne.com/AppIssueBadWord.html


Also, "The Ten Commandments of Access"

http://mvps.org/access/tencommandments.htm


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Roger Carlson said:
Then there's something going on outside of your IIF statement. "Name" might
actually be tripping it up, or it could be something else. You need to look
farther afield.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Thanks for the reply... Yours was one of the combinations of attempts
that I
had tried previously. But, I did paste your IIF below and I am still only
returning the "_" for all records regardless of wild card text content
criteria being met or not met. This one is really stumping me and holding
me
up! Thank you for the avoid field "Name" tip. I will keep in mind for
future databases.

:

I don't really know why your IIF is returning just "_", but you should
avoid
naming your fields or controls "Name" since that is a reserved word and
sometimes causes confusion.

The real problem I see with your statement is that you are only
evaluating
[ExpemptionType] with a value of S & L in your name field. You need to
put
parenthases around all of the OR conditions:

=IIf([ExemptionType]="B" AND ([Name] Like "*S & L*" OR [Name] Like
"*Saving*" OR [Name] Like "*SVG*" OR [Name] Like "*S&L*"),"X","_")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the
text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not
looking
at
the wild card text conditions.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
mcduff22 said:
On my report, I have an unbound field that should contain an "X" if the
[ExemptionType] field = "B" AND the [NAME] field contains any of the text
combination "Saving", "S & L", "SVG", or "S&L" else place "_" in the
unbound
field.

=IIf([ExemptionType]="B" AND [Name] Like "*S & L*" OR [Name] Like
"*Saving*"
OR [Name] Like "*SVG*" OR [Name] Like "*S&L*","X","_")

Right now, I only get the "_" for all records. The field is not looking
at
the wild card text conditions.

Thanks!
Click to expand...
 
Back
Top