IIF statement with check Box

[Gus Chuch]

I seem to be missing something? I got a SQL with a criteria that uses the IIF
statement. If the check box on my form is true, filter by txtCity, which
seems to work OK. If my check box on my form is false select all (like = “*â€)
which this does not work, I get no selection at all. Anyone have any ideas?

IIf((([Forms]![frmMailingList]![chkCity])=True),
([Forms]![frmMailingList]![txtCity]),
(([tblMailingList].[CITY]) Like ([tblMailingList].[CITY])="*")))

 

[Steve Schapel]

Gus,

You can't really use an IIf() function to control/define query criteria
like this. A function evaluates to a value, where you are apparently
trying to get it to evaluate to an expression.

One way to write the criteria you want would result in a Where clause
like this...
WHERE (([Forms]![frmMailingList]![chkCity]=True AND
[CITY]=[Forms]![frmMailingList]![txtCity]) OR
([Forms]![frmMailingList]![chkCity]=False))

To do this in the query designer, put [Forms]![frmMailingList]![chkCity]
in the Field line of a blank column in the query design grid. Then in
the first Criteria row of the grid in this column enter True and in the
Criteria of your City column, enter [Forms]![frmMailingList]![txtCity].
Then in the second criteria row of the
[Forms]![frmMailingList]![chkCity] column, enter False.

 

[Marshall Barton]

I seem to be missing something? I got a SQL with a criteria that uses the IIF
statement. If the check box on my form is true, filter by txtCity, which
seems to work OK. If my check box on my form is false select all (like = “*”)
which this does not work, I get no selection at all. Anyone have any ideas?

IIf((([Forms]![frmMailingList]![chkCity])=True),
([Forms]![frmMailingList]![txtCity]),
(([tblMailingList].[CITY]) Like ([tblMailingList].[CITY])="*")))

No need for all that. Just use the criteria:

Forms!frmMailingList!txtCity OR
(Forms!frmMailingList!txtCity = False)

 

[Ken Snell \(MVP\)]

You cannot put the entire search clause within the IIf statement; it is used
to return a specific value. However, in order to suggest an alternative
setup, give us the entire SQL statement that you're currently trying to use.
It's not at all clear how you wanted to use this IIf statement.

 

[fredg]

I seem to be missing something? I got a SQL with a criteria that uses the IIF
statement. If the check box on my form is true, filter by txtCity, which
seems to work OK. If my check box on my form is false select all (like = ´*¡)
which this does not work, I get no selection at all. Anyone have any ideas?

IIf((([Forms]![frmMailingList]![chkCity])=True),
([Forms]![frmMailingList]![txtCity]),
(([tblMailingList].[CITY]) Like ([tblMailingList].[CITY])="*")))

Using the check box....
Where tblMailingList.City Like
IIf([Forms]![frmMailingList]![chkCity]=-1,
[Forms]![frmMailingList]![txtCity],"*")

But you don't need the check box.
If the txtCity is null, just return all records.

Where tblMailingList.City Like
IIf(IsNull([Forms]![frmMailingList]![chkCity]),"*",
[Forms]![frmMailingList]![txtCity])

 

[Gus Chuch]

Thanks for the help the chkCity seem’s to work but it messed up the operation
of two other crterias I got. One’s for Location_Type and the other is County.

((tblMailingList.[Location
Type])=[Forms]![frmMailingList]![cboLocationType]) AND
((tblMailingList.County)=[Forms]![frmMailingList]![cboCounty])

I plan on having two more check box one for Location_Type and County. So
that on my report (Mail Flyers) I can filter by, let’s say Location Type or
narrow it down to the County or again by City. If you need the hold SQL I’ll
send it on Tuesday.
But I got to stop working on this for now; I got an Oracle9i test on Monday
I got to study for.
--
thank You

Gus,

You can't really use an IIf() function to control/define query criteria
like this. A function evaluates to a value, where you are apparently
trying to get it to evaluate to an expression.

One way to write the criteria you want would result in a Where clause
like this...
WHERE (([Forms]![frmMailingList]![chkCity]=True AND
[CITY]=[Forms]![frmMailingList]![txtCity]) OR
([Forms]![frmMailingList]![chkCity]=False))

To do this in the query designer, put [Forms]![frmMailingList]![chkCity]
in the Field line of a blank column in the query design grid. Then in
the first Criteria row of the grid in this column enter True and in the
Criteria of your City column, enter [Forms]![frmMailingList]![txtCity].
Then in the second criteria row of the
[Forms]![frmMailingList]![chkCity] column, enter False.

--
Steve Schapel, Microsoft Access MVP

I seem to be missing something? I got a SQL with a criteria that uses the IIF
statement. If the check box on my form is true, filter by txtCity, which
seems to work OK. If my check box on my form is false select all (like = “*â€)
which this does not work, I get no selection at all. Anyone have any ideas?

IIf((([Forms]![frmMailingList]![chkCity])=True),
([Forms]![frmMailingList]![txtCity]),
(([tblMailingList].[CITY]) Like ([tblMailingList].[CITY])="*")))

 

[Steve Schapel]

Gus,

It is not clear what your purpose is with these checkboxes. But as
others who responded to you in this thread have intimated, you are
probably doing this more complicated that necessary.