Problems with AND in multiple IIF statements

[jmoore]

I have several text boxes in a subreport that have similar IIf statements to
the one below, except this is the only one that has an AND in it. The others
all work fine. This one results in an invalid syntax message. The subreport
is based on a query with both fields included. I tried adding parens around
the AND section, but received the same message. What am I missing???

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,“File Not Availâ€,
IIf[F7]=#1/1/7777#,"Not Avail", IIf[F7]=#1/1/9999#,"Not
App",IIf[F7]=#9/9/9999#,"Missing",[F7]))))

Thanks!

 

[KARL DEWEY]

Are [FileNotAvail] and [F7] fields in the subform source or are they derived
from somewhere else?

 

[jmoore]

Yes, both [FileNotAvail] and [F7] are in the query that the subreport is
based on. I received the error message whether they were invisible controls

Are [FileNotAvail] and [F7] fields in the subform source or are they derived
from somewhere else?

--
Build a little, test a little.

I have several text boxes in a subreport that have similar IIf statements to
the one below, except this is the only one that has an AND in it. The others
all work fine. This one results in an invalid syntax message. The subreport
is based on a query with both fields included. I tried adding parens around
the AND section, but received the same message. What am I missing???

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,“File Not Availâ€,
IIf[F7]=#1/1/7777#,"Not Avail", IIf[F7]=#1/1/9999#,"Not
App",IIf[F7]=#9/9/9999#,"Missing",[F7]))))

Thanks!

 

[KARL DEWEY]

I would suggest making a calculated field in the query and using that in the
report.
--
Build a little, test a little.

Yes, both [FileNotAvail] and [F7] are in the query that the subreport is
based on. I received the error message whether they were invisible controls

Are [FileNotAvail] and [F7] fields in the subform source or are they derived
from somewhere else?

--
Build a little, test a little.

I have several text boxes in a subreport that have similar IIf statements to
the one below, except this is the only one that has an AND in it. The others
all work fine. This one results in an invalid syntax message. The subreport
is based on a query with both fields included. I tried adding parens around
the AND section, but received the same message. What am I missing???

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,“File Not Availâ€,
IIf[F7]=#1/1/7777#,"Not Avail", IIf[F7]=#1/1/9999#,"Not
App",IIf[F7]=#9/9/9999#,"Missing",[F7]))))

Thanks!

 

[John Spencer]

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,"File Not Avail"
, IIf([F7]=#1/1/7777#,"Not Avail"
, IIf([F7]=#1/1/9999#,"Not App"
, IIf([F7]=#9/9/9999#,"Missing",[F7]))))

I don't know if you typed this into the posting or copied and pasted it. The
best method is to copy and paste.

Your post had no open parens after the 2nd, 3rd and 4th IIF functions.
Also the post used "smart" quotes for the first result.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Yes, both [FileNotAvail] and [F7] are in the query that the subreport is
based on. I received the error message whether they were invisible controls

Are [FileNotAvail] and [F7] fields in the subform source or are they derived
from somewhere else?

--
Build a little, test a little.

I have several text boxes in a subreport that have similar IIf statements to
the one below, except this is the only one that has an AND in it. The others
all work fine. This one results in an invalid syntax message. The subreport
is based on a query with both fields included. I tried adding parens around
the AND section, but received the same message. What am I missing???

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,“File Not Availâ€,
IIf[F7]=#1/1/7777#,"Not Avail", IIf[F7]=#1/1/9999#,"Not
App",IIf[F7]=#9/9/9999#,"Missing",[F7]))))

Thanks!

 

[jmoore]

I can do that, but would like to keep it in the report since I have many text
boxes with similar criteria (example below) which produce correct
information. I don't understand why it does not work in the report just
because one IIf includes criteria for two fields. Why wouldn't it work
when using AND? Thanks for helping me clear up this question.

=IIf([F7]=#1/1/7777#,"Not Avail",IIf([F7]=#1/1/9999#,"Not
App",IIf([F7]=#9/9/9999#,"Missing",IIf([F7]=#8/8/8888#,"File Not
Avail",[F7]))))

I would suggest making a calculated field in the query and using that in the
report.
--
Build a little, test a little.

Yes, both [FileNotAvail] and [F7] are in the query that the subreport is
based on. I received the error message whether they were invisible controls

Are [FileNotAvail] and [F7] fields in the subform source or are they derived
from somewhere else?

--
Build a little, test a little.


:

I have several text boxes in a subreport that have similar IIf statements to
the one below, except this is the only one that has an AND in it. The others
all work fine. This one results in an invalid syntax message. The subreport
is based on a query with both fields included. I tried adding parens around
the AND section, but received the same message. What am I missing???

=IIf([FileNotAvail]=1 AND [F7]=#1/1/7777#,“File Not Availâ€,
IIf[F7]=#1/1/7777#,"Not Avail", IIf[F7]=#1/1/9999#,"Not
App",IIf[F7]=#9/9/9999#,"Missing",[F7]))))

Thanks!