Simple IIF Question

[G]

I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I would
like to show records depending on a selection in the search form [selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary

 

[Douglas J. Steele]

[Forms]![Selection] isn't valid: you're missing either the name of the form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2

 

[G]

Sorry about the confusion, I didn't copy the correct IIF statement ... here
it is:

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

The problem still exists ... when [selection]=2, the query shows no records.
When I remove this criteria from this field, all records appear (so I know
that this formula is causing the issue).

Any suggestions? Thanks.

[Forms]![Selection] isn't valid: you're missing either the name of the form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I would
like to show records depending on a selection in the search form
[selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary

 

[Douglas J. Steele]

You cannot put an inequality into the IIf statement like that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry about the confusion, I didn't copy the correct IIF statement ...
here
it is:

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

The problem still exists ... when [selection]=2, the query shows no
records.
When I remove this criteria from this field, all records appear (so I know
that this formula is causing the issue).

Any suggestions? Thanks.

[Forms]![Selection] isn't valid: you're missing either the name of the
form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want
to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I
would
like to show records depending on a selection in the search form
[selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary

 

[John Spencer (MVP)]

You can do this with some complex criteria.

Where (SomeField >-1 AND [Forms]![frmSwitchboard]![selection]=2) OR
(SomeField = 0 AND [Forms]![frmSwitchboard]![selection] <> 2)

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

Sorry about the confusion, I didn't copy the correct IIF statement ... here
it is:

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

The problem still exists ... when [selection]=2, the query shows no records.
When I remove this criteria from this field, all records appear (so I know
that this formula is causing the issue).

Any suggestions? Thanks.

[Forms]![Selection] isn't valid: you're missing either the name of the form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I would
like to show records depending on a selection in the search form
[selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary

 

[G]

Thanks for the response ... I just need a little clarification ... what do I
use (both WHERE and IIF?) and where do I use it (Criteria field in the
query?)? Thanks.

G

You can do this with some complex criteria.

Where (SomeField >-1 AND [Forms]![frmSwitchboard]![selection]=2) OR
(SomeField = 0 AND [Forms]![frmSwitchboard]![selection] <> 2)

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

Sorry about the confusion, I didn't copy the correct IIF statement ... here
it is:

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

The problem still exists ... when [selection]=2, the query shows no records.
When I remove this criteria from this field, all records appear (so I know
that this formula is causing the issue).

Any suggestions? Thanks.

[Forms]![Selection] isn't valid: you're missing either the name of the form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I would
like to show records depending on a selection in the search form
[selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary

 

[John Spencer (MVP)]

Sorry, I left an artifact in there. You would use just the where clause. If
you are doing this in the grid then.

You need to add a new column

Field: Expr1: [Forms]![frmSwitchboard]![selection]
CriteriaLine1: 2
CriteriaLine2: <> 2

Field:SomeField
CriteriaLine1: >-1
CriteriaLine2: 0

That along with all your other criteria. It may get too complex for Access to
handle, but it is hard to tell without trying.

Thanks for the response ... I just need a little clarification ... what do I
use (both WHERE and IIF?) and where do I use it (Criteria field in the
query?)? Thanks.

G

You can do this with some complex criteria.

Where (SomeField >-1 AND [Forms]![frmSwitchboard]![selection]=2) OR
(SomeField = 0 AND [Forms]![frmSwitchboard]![selection] <> 2)

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

Sorry about the confusion, I didn't copy the correct IIF statement ... here
it is:

IIf([Forms]![frmSwitchboard]![selection]=2,>-1,0)

The problem still exists ... when [selection]=2, the query shows no records.
When I remove this criteria from this field, all records appear (so I know
that this formula is causing the issue).

Any suggestions? Thanks.

:

[Forms]![Selection] isn't valid: you're missing either the name of the form
or the name of the control (can't tell which!) In other words, you need
[Forms]![NameOfForm]![NameOfControl]

Assuming you want to return all records where the value in [numberfield]
matches the value in [Forms]![NameOfForm]![NameOfControl], except when
[Forms]![NameOfForm]![NameOfControl] contains 2 (in which case you want to
return all records), try:

[Forms]![NameOfForm]![NameOfControl] OR
([Forms]![NameOfForm]![NameOfControl] = 2)

in your criteria field. In the SQL, this should equate to

WHERE [numberfield] = [Forms]![NameOfForm]![NameOfControl]
OR [Forms]![NameOfForm]![NameOfControl] = 2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm having a problem with an IIF statement.

I have a query to select records from a table for a search form. I would
like to show records depending on a selection in the search form
[selection].
I have a number field in the table that I'm using for the selection
[numberfield].

I've set the following crteria for the [numberfield]:

IIf([Forms]![selection]=2,>-1,0)

The problem is that no records are appearing when the [selection]=2. I
tried (like "*") as criteria for a true result and that doesn't work.

Does anyone have any suggestions?

Thanks.

Gary