Use Or in function

[alex]

Using Access ‘03

I’m trying to get this function to work in the criteria of a query:
IIf([forms]![frmA]![comBox]="Combined","Value1" Or “Value2”, [forms]!
[frmA]![comBox])

It returns an error saying the function is too complicated.

The problem is the ‘Or’ inside the function.

This works:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])

As does this:
"Value1" Or “Value2”

Is there a trick to the IIF function? Or, do I need a different one?

I suppose I could do this on two lines in the QBE:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])
IIf([forms]![frmA]![comBox]="Combined","Value2" , [forms]![frmA]!
[comBox])

But that seems a bit redundant.

Thanks,
alex

 

[Dirk Goldgar]

Could you explain in words what you want your IIf expression to do? I can't
work it out.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Using Access ‘03

I’m trying to get this function to work in the criteria of a query:
IIf([forms]![frmA]![comBox]="Combined","Value1" Or “Value2”, [forms]!
[frmA]![comBox])

It returns an error saying the function is too complicated.

The problem is the ‘Or’ inside the function.

This works:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])

As does this:
"Value1" Or “Value2”

Is there a trick to the IIF function? Or, do I need a different one?

I suppose I could do this on two lines in the QBE:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])
IIf([forms]![frmA]![comBox]="Combined","Value2" , [forms]![frmA]!
[comBox])

But that seems a bit redundant.

Thanks,
alex

 

[John W. Vinson]

Using Access ‘03

I’m trying to get this function to work in the criteria of a query:
IIf([forms]![frmA]![comBox]="Combined","Value1" Or “Value2”, [forms]!
[frmA]![comBox])

It returns an error saying the function is too complicated.

The problem is the ‘Or’ inside the function.

This works:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])

As does this:
"Value1" Or “Value2”

Is there a trick to the IIF function? Or, do I need a different one?

I suppose I could do this on two lines in the QBE:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])
IIf([forms]![frmA]![comBox]="Combined","Value2" , [forms]![frmA]!
[comBox])

But that seems a bit redundant.

Thanks,
alex

The IIF can return *a value* - it can't return a query operator such as OR.

If (and it's not clear from your post) that you want to return all records if
the combo box contains "combined", use a criterion

=Forms!frmA!comBox OR Forms!frmA!comBox = "Combined"

with no function call at all; if you want it to only return records with
Value1 or Value2 (and not any other values) use

=Forms!frmA!comBox OR (Forms!frmA!comBox = "Combined" AND field IN("Value1",
"Value2")

 

[alex]

Could you explain in words what you want your IIf expression to do?  I can't
work it out.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)


Using Access ‘03

I’m trying to get this function to work in the criteria of a query:
IIf([forms]![frmA]![comBox]="Combined","Value1" Or “Value2”, [forms]!
[frmA]![comBox])

It returns an error saying the function is too complicated.

The problem is the ‘Or’ inside the function.

This works:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])

As does this:
"Value1" Or “Value2”

Is there a trick to the IIF function?  Or, do I need a different one?

I suppose I could do this on two lines in the QBE:
IIf([forms]![frmA]![comBox]="Combined","Value1" , [forms]![frmA]!
[comBox])
IIf([forms]![frmA]![comBox]="Combined","Value2" , [forms]![frmA]!
[comBox])

But that seems a bit redundant.

Thanks,
alex

Sorry if my post seemed confusing…

In the criteria section of my query, I have a reference to the value
of a combo box on a form.

If the value of the combo box = “Combined” (which is simply a combo
box value) then I want the query to return two hardcoded values; I
just gave them names of Value1 and Value2. The actual values are
“GREEN” and “BLUE”...So in the criteria it's "GREEN" or "BLUE"

Else, return the value of the combo box which will be something other
than “Combined.”

Hope that helps.

 

[Dirk Goldgar]

This would be one version of a criteria expression to be put in the query
designer:

[Forms]![frmA]![comBox] Or
IIf([Forms]![frmA]![comBox]="Combined","GREEN",Null) Or
IIf([Forms]![frmA]![comBox]="Combined","BLUE",Null)

I've broken that into three lines for posting, but really it should be all
one line.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



Sorry if my post seemed confusing…

In the criteria section of my query, I have a reference to the value
of a combo box on a form.

If the value of the combo box = “Combined” (which is simply a combo
box value) then I want the query to return two hardcoded values; I
just gave them names of Value1 and Value2. The actual values are
“GREEN” and “BLUE”...So in the criteria it's "GREEN" or "BLUE"

Else, return the value of the combo box which will be something other
than “Combined.”

 

[alex]

This would be one version of a criteria expression to be put in the query
designer:

    [Forms]![frmA]![comBox] Or
    IIf([Forms]![frmA]![comBox]="Combined","GREEN",Null) Or
    IIf([Forms]![frmA]![comBox]="Combined","BLUE",Null)

I've broken that into three lines for posting, but really it should be all
one line.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)



Sorry if my post seemed confusing…

In the criteria section of my query, I have a reference to the value
of a combo box on a form.

If the value of the combo box = “Combined” (which is simply a combo
box value) then I want the query to return two hardcoded values; I
just gave them names of Value1 and Value2.  The actual values are
“GREEN” and “BLUE”...So in the criteria it's "GREEN" or "BLUE"

Else, return the value of the combo box which will be something other
than “Combined.”

Thanks John and Dirk; that helped me figure this out.
alex