IIF Syntax?

S

SAC

I cannot get a true response from thsi syntax:

IIf(([Shows].[H/T Status]) Like [forms]![show schedule]![SS_SP]="Tentative
or Definite",([Shows].[H/T Status])="Tentative" Or ([Shows].[H/T
Status])="Definite","Definite")

I've made sure that the control = "Tentative or Definite"

I want all shows which has a status of definite or tentative after a combo
box is selected that has "Tentative or Definite" as a choice.

I can put "Tentative" or "Definite" in the query's criteria and it works
fine.

Thanks.
 
J

John Vinson

I cannot get a true response from thsi syntax:

IIf(([Shows].[H/T Status]) Like [forms]![show schedule]![SS_SP]="Tentative
or Definite",([Shows].[H/T Status])="Tentative" Or ([Shows].[H/T
Status])="Definite","Definite")

I've made sure that the control = "Tentative or Definite"

I want all shows which has a status of definite or tentative after a combo
box is selected that has "Tentative or Definite" as a choice.

I can put "Tentative" or "Definite" in the query's criteria and it works
fine.

Thanks.
Click to expand...

Several errors here.

You're mixing logic in your IIF statement. The first argument to IIF
should be a logical statement which evaluates to either TRUE or FALSE.
In this case, you're asking if the table field [H/T Status] is equal
to (LIKE and = are of identical effect if you don't have wildcards)
the result of the expression

[Forms]![show schedule]![SS_SP] = "Tentative or Definite"

Now this expression might be TRUE if SS_SP contains the exact text
string "Tentative or Definite", or it might be FALSE - which will work
only if H/T Status is a Boolean field. Is it?

The more critical error is that you CANNOT pass an operator such as
LIKE or OR in a parameter. A query parameter, such as the result of an
IIF() function call, should contain only the actual value being
searched.

I'd use a completely different query logic:

WHERE ([forms]![show schedule]![SS_SP]="Tentative or Definite" AND
[Shows].[H/T Status] IN ("Tentative", "Definite")
OR )[Shows].[H/T Status] = [forms]![show schedule]![SS_SP])


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
S

SAC

I'm sorry I screwed up the first syntax. H/T Status is a drop down box with
a single column of text type into it. One of the choices is "Tentative or
Definite" Other choices are "Tentative", "Definite"

I'm trying to give them a report that has either "Definites", "Tentatives"
or "Definites and Tentatives" as their choices for the report.

The "Tentative" and the "definite" choices are not a problem, but the
"Tentative and Definite" choice is a problem". I have matched the text with
the form.

I want them to be able to pick the choice "Tentative or Definite" and have
the query select records that are either "Tentative" or "Definite"

Here's what I've come up woith so far and it still doesn't work:

IIf([forms]![show schedule]![SS_Stat]="Tentative or Definite","Tentative" Or
"Definite","Definite")

Thanks

John Vinson said:
I cannot get a true response from thsi syntax:

IIf(([Shows].[H/T Status]) Like [forms]![show schedule]![SS_SP]="Tentative
or Definite",([Shows].[H/T Status])="Tentative" Or ([Shows].[H/T
Status])="Definite","Definite")

I've made sure that the control = "Tentative or Definite"

I want all shows which has a status of definite or tentative after a combo
box is selected that has "Tentative or Definite" as a choice.

I can put "Tentative" or "Definite" in the query's criteria and it works
fine.

Thanks.
Click to expand...

Several errors here.

You're mixing logic in your IIF statement. The first argument to IIF
should be a logical statement which evaluates to either TRUE or FALSE.
In this case, you're asking if the table field [H/T Status] is equal
to (LIKE and = are of identical effect if you don't have wildcards)
the result of the expression

[Forms]![show schedule]![SS_SP] = "Tentative or Definite"

Now this expression might be TRUE if SS_SP contains the exact text
string "Tentative or Definite", or it might be FALSE - which will work
only if H/T Status is a Boolean field. Is it?

The more critical error is that you CANNOT pass an operator such as
LIKE or OR in a parameter. A query parameter, such as the result of an
IIF() function call, should contain only the actual value being
searched.

I'd use a completely different query logic:

WHERE ([forms]![show schedule]![SS_SP]="Tentative or Definite" AND
[Shows].[H/T Status] IN ("Tentative", "Definite")
OR )[Shows].[H/T Status] = [forms]![show schedule]![SS_SP])


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
S

SAC

Here's another thing. I'm almost there.

This works - when only Tentative is selected when it's true:

IIf([forms]![show schedule]![SS_Stat]="Tentative or
Definite","Tentative","Definite")

This doesn't work:

IIf([forms]![show schedule]![SS_Stat]="Tentative or Definite","Tentative" Or
"Definite","Definite")

Thanks again.

John Vinson said:
I cannot get a true response from thsi syntax:

IIf(([Shows].[H/T Status]) Like [forms]![show schedule]![SS_SP]="Tentative
or Definite",([Shows].[H/T Status])="Tentative" Or ([Shows].[H/T
Status])="Definite","Definite")

I've made sure that the control = "Tentative or Definite"

I want all shows which has a status of definite or tentative after a combo
box is selected that has "Tentative or Definite" as a choice.

I can put "Tentative" or "Definite" in the query's criteria and it works
fine.

Thanks.
Click to expand...

Several errors here.

You're mixing logic in your IIF statement. The first argument to IIF
should be a logical statement which evaluates to either TRUE or FALSE.
In this case, you're asking if the table field [H/T Status] is equal
to (LIKE and = are of identical effect if you don't have wildcards)
the result of the expression

[Forms]![show schedule]![SS_SP] = "Tentative or Definite"

Now this expression might be TRUE if SS_SP contains the exact text
string "Tentative or Definite", or it might be FALSE - which will work
only if H/T Status is a Boolean field. Is it?

The more critical error is that you CANNOT pass an operator such as
LIKE or OR in a parameter. A query parameter, such as the result of an
IIF() function call, should contain only the actual value being
searched.

I'd use a completely different query logic:

WHERE ([forms]![show schedule]![SS_SP]="Tentative or Definite" AND
[Shows].[H/T Status] IN ("Tentative", "Definite")
OR )[Shows].[H/T Status] = [forms]![show schedule]![SS_SP])


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
J

John Vinson

Here's another thing. I'm almost there.

This works - when only Tentative is selected when it's true:

IIf([forms]![show schedule]![SS_Stat]="Tentative or
Definite","Tentative","Definite")

This doesn't work:

IIf([forms]![show schedule]![SS_Stat]="Tentative or Definite","Tentative" Or
"Definite","Definite")
Click to expand...

You're correct. This will not work, and *IT CANNOT BE MADE TO WORK*.

You're attempting to pass the logical operator OR in a Parameter.
Access *will not accept* logical operators in parameters.

Please note my suggestion in the previous message, and try it: because
it *will* work, unlike trying to pass an operator in a paramter!

To reiterate (with a mistyped parenthesis corrected):

I'd use a completely different query logic:

WHERE ([forms]![show schedule]![SS_SP]="Tentative or Definite" AND
[Shows].[H/T Status] IN ("Tentative", "Definite"))
OR ([Shows].[H/T Status] = [forms]![show schedule]![SS_SP])

The first criterion will be true if the combo box contains the text
string "Tentative or Definite" and [H/T Status] has either one of
those two values; the second criterion will return records where the
text in the combo box is exactly equal to the text in the field (that
is, the user might select "Tentative" or they might select
"Definite").

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
S

SAC

OH! Thanks! I was kinda think-headed on the first one becasue I had
mis-typed some things!

Thanks!

John Vinson said:
Here's another thing. I'm almost there.

This works - when only Tentative is selected when it's true:

IIf([forms]![show schedule]![SS_Stat]="Tentative or
Definite","Tentative","Definite")

This doesn't work:

IIf([forms]![show schedule]![SS_Stat]="Tentative or Definite","Tentative" Or
"Definite","Definite")
Click to expand...

You're correct. This will not work, and *IT CANNOT BE MADE TO WORK*.

You're attempting to pass the logical operator OR in a Parameter.
Access *will not accept* logical operators in parameters.

Please note my suggestion in the previous message, and try it: because
it *will* work, unlike trying to pass an operator in a paramter!

To reiterate (with a mistyped parenthesis corrected):

I'd use a completely different query logic:

WHERE ([forms]![show schedule]![SS_SP]="Tentative or Definite" AND
[Shows].[H/T Status] IN ("Tentative", "Definite"))
OR ([Shows].[H/T Status] = [forms]![show schedule]![SS_SP])

The first criterion will be true if the combo box contains the text
string "Tentative or Definite" and [H/T Status] has either one of
those two values; the second criterion will return records where the
text in the combo box is exactly equal to the text in the field (that
is, the user might select "Tentative" or they might select
"Definite").

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Top