IIF Syntax error

  • Thread starter Thread starter Archer
  • Start date Start date
A

Archer

I am trying to include the following condition in the control source of a label

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€))+
IIf([Number of Courses]>=2 ,IFF([Course 2 Enrolled?] = “Noâ€,â€Course 2â€))
+IIf([Number of Courses]>=3 ,IFF([Course 3 Enrolled?] = “Noâ€,â€Course3â€))

and I get an Syntax Error.

I tried the following variations and these are the results i got

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€))
Result: Syntax Error

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€,
""),"")
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,â€Course1â€)
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,â€Course1â€,""),"")
Result: Syntax Error

Can anyone help out?
[Number of Courses] is a field from Table Courses
[Course 1 Enrolled] is a field from another Table Student
 
First of all the function is IIF not IFF.
Second, are the field Course 1 Enrolled Text fields or are they boolean
(Yes/No) fields?
Assuming they are Boolean (Yes/No) fields, then I would expect the
following.

=IIf([Number of Courses]>=1 ,IIF([Course 1 Enrolled?] = False,"Course1"))+
IIf([Number of Courses]>=2 ,IIF([Course 2 Enrolled?] = False,"Course 2"))
+IIf([Number of Courses]>=3 ,IIF([Course 3 Enrolled?] = False,"Course3"))

By the way, I edited out the "Smart Quotes" in your expression and changed
them to standard quote marks.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Archer said:
I am trying to include the following condition in the control source of a label

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”))+
IIf([Number of Courses]>=2 ,IFF([Course 2 Enrolled?] = “No”,”Course 2”))
+IIf([Number of Courses]>=3 ,IFF([Course 3 Enrolled?] = “No”,”Course3”))

and I get an Syntax Error.

I tried the following variations and these are the results i got

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”))
Result: Syntax Error

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”,
""),"")
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “No”,”Course1”)
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “No”,”Course1”,""),"")
Result: Syntax Error

Can anyone help out?
[Number of Courses] is a field from Table Courses
[Course 1 Enrolled] is a field from another Table Student
Click to expand...


Sometimes, you spelled IIf as IFF

I don't see a syntax error in this one:
=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “No”,
”Course1”)

The last one has an extra argument:
=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “No”,
”Course1”,"")

But the error message may be misleading if the field names
are not in the form's record source, the fields are a
different type than the values you are comparing them to, or
??

How are you getting the fields from both tables into your
form? It is very important for us to know what fields are
in which tables as well as the form's record source table or
it's record source query's SQL statement.

If the form's record source is table Student, then you could
use the DLookup function to retrieve the value of the
[Number of Courses] field.
 
Hey John,
That did solve my problem partially only to bring up another one.
If i write something like

=IIF([Course 1 Status] = False,"Course1")
and if condition is met i get the value

However
1. When i combine more than one IIF statements
=IIF([Course 1 Status] = False," Course 1")+ IIF([Course 2 Status] = False,"
Course 2") + IIF([Course 3 Status] = False, " Course 3")

I hope to get result as

Course 1 Course 2 Course 3

if all conditions are met. However I get no value returned.

Further more:
2. for the statement,
IIf([Number of Courses]>=3,IIf([Course 3 Status]=False,"Course 3"))
I am not getting the value. Any combination of these conditions of more
courses also results in no output.



John Spencer said:
First of all the function is IIF not IFF.
Second, are the field Course 1 Enrolled Text fields or are they boolean
(Yes/No) fields?
Assuming they are Boolean (Yes/No) fields, then I would expect the
following.

=IIf([Number of Courses]>=1 ,IIF([Course 1 Enrolled?] = False,"Course1"))+
IIf([Number of Courses]>=2 ,IIF([Course 2 Enrolled?] = False,"Course 2"))
+IIf([Number of Courses]>=3 ,IIF([Course 3 Enrolled?] = False,"Course3"))

By the way, I edited out the "Smart Quotes" in your expression and changed
them to standard quote marks.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Archer said:
I am trying to include the following condition in the control source of a
label

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = "No","Course1"))+
IIf([Number of Courses]>=2 ,IFF([Course 2 Enrolled?] = "No","Course 2"))
+IIf([Number of Courses]>=3 ,IFF([Course 3 Enrolled?] = "No","Course3"))

and I get an Syntax Error.

I tried the following variations and these are the results i got

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = "No","Course1"))
Result: Syntax Error

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = "No","Course1",
""),"")
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = "No","Course1")
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = "No","Course1",""),"")
Result: Syntax Error

Can anyone help out?
[Number of Courses] is a field from Table Courses
[Course 1 Enrolled] is a field from another Table Student
Click to expand...
Click to expand...
 
Hey John, Marshal,

I resolved the problem by replacing + by &

thank you for your help
Archer

Marshall Barton said:
Archer said:
I am trying to include the following condition in the control source of a label

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€))+
IIf([Number of Courses]>=2 ,IFF([Course 2 Enrolled?] = “Noâ€,â€Course 2â€))
+IIf([Number of Courses]>=3 ,IFF([Course 3 Enrolled?] = “Noâ€,â€Course3â€))

and I get an Syntax Error.

I tried the following variations and these are the results i got

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€))
Result: Syntax Error

=IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “Noâ€,â€Course1â€,
""),"")
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,â€Course1â€)
Result: Syntax Error

=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,â€Course1â€,""),"")
Result: Syntax Error

Can anyone help out?
[Number of Courses] is a field from Table Courses
[Course 1 Enrolled] is a field from another Table Student
Click to expand...


Sometimes, you spelled IIf as IFF

I don't see a syntax error in this one:
=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,
â€Course1â€)

The last one has an extra argument:
=IIf([Number of Courses]>=1 And [Course 1 Enrolled?] = “Noâ€,
â€Course1â€,"")

But the error message may be misleading if the field names
are not in the form's record source, the fields are a
different type than the values you are comparing them to, or
??

How are you getting the fields from both tables into your
form? It is very important for us to know what fields are
in which tables as well as the form's record source table or
it's record source query's SQL statement.

If the form's record source is table Student, then you could
use the DLookup function to retrieve the value of the
[Number of Courses] field.
Click to expand...
 
Back
Top