Two Criteria

[Picos]

I need a Criteria expression that take one of two options about a combobox's
value in a FormCriteria:

If the value is a City, then the Query only shows records with this value in
the IdCity field.

If the combobox is null, then show all records (Without considering the
IdCity field).

I need an expression (not VBA code), I tried this in the IdCity field:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),>0,[Forms]![formCriteria]![cmbCity])

But the Query shows 0 rows If the combobox is null.

I will appreciate any help...

Thanks a lot.
And sorry for my english.

 

[Ken Snell \(MVP\)]

Try this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),True,[Forms]![formCriteria]![cmbCity])

 

[Picos]

Ken: thanks for your help, but unfortunaly it dosn´t works
¿Do you have any other idea?

If I try with a specific value it works, like this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),1,[Forms]![formCriteria]![cmbCity])

But I need all records, in other words, just like the criteria cell is in
blank.

--
Oscar Picos
Office Autom

Try this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),True,[Forms]![formCriteria]![cmbCity])


--

Ken Snell
<MS ACCESS MVP>

I need a Criteria expression that take one of two options about a
combobox's
value in a FormCriteria:

If the value is a City, then the Query only shows records with this value
in
the IdCity field.

If the combobox is null, then show all records (Without considering the
IdCity field).

I need an expression (not VBA code), I tried this in the IdCity field:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),>0,[Forms]![formCriteria]![cmbCity])

But the Query shows 0 rows If the combobox is null.

I will appreciate any help...

Thanks a lot.
And sorry for my english.

 

[Ken Snell \(MVP\)]

It will help if you post the entire SQL statement of the query. The reason
what I suggested isn't working likely is because of how this expression is
being used right now.
--

Ken Snell
<MS ACCESS MVP>

Ken: thanks for your help, but unfortunaly it dosn´t works
¿Do you have any other idea?

If I try with a specific value it works, like this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),1,[Forms]![formCriteria]![cmbCity])

But I need all records, in other words, just like the criteria cell is in
blank.

--
Oscar Picos
Office Autom

Try this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),True,[Forms]![formCriteria]![cmbCity])


--

Ken Snell
<MS ACCESS MVP>

I need a Criteria expression that take one of two options about a
combobox's
value in a FormCriteria:

If the value is a City, then the Query only shows records with this
value
in
the IdCity field.

If the combobox is null, then show all records (Without considering the
IdCity field).

I need an expression (not VBA code), I tried this in the IdCity field:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),>0,[Forms]![formCriteria]![cmbCity])

But the Query shows 0 rows If the combobox is null.

I will appreciate any help...

Thanks a lot.
And sorry for my english.

 

[Gary Walter]

pardon me for butting in....

it sounds like you are using the design grid:

Field: IdCity
Table: yourtable
Sort:
Show: <checked>
Criteria: [Forms]![formCriteria]![cmbCity]
Or:

Field: [Forms]![formCriteria]![cmbCity]
Table:
Sort:
Show: <unchecked>
Criteria:
Or: IS NULL

in SQL, would look like:

WHERE
IdCity = [Forms]![formCriteria]![cmbCity]
OR
[Forms]![formCriteria]![cmbCity] IS NULL;

"Picos"wrote:

 

[Ken Snell \(MVP\)]

Thanks. The SQL statement makes it easier to see what you're trying to do.
Jamie's solution is the one to use.

--

Ken Snell
<MS ACCESS MVP>

OK, the SQL statement is (Fields & tables are in spanish):

SELECT Clientes.Nombre, Lst_Ciudades.Ciudad
FROM Lst_Ciudades INNER JOIN Clientes ON Lst_Ciudades.IdCd = Clientes.IdCd
WHERE
(((Lst_Ciudades.IdCd)=IIf(IsNull([Forms]![formCriteria]![Ciudad]),True,[Forms]![formCriteria]![Ciudad])));

Thanks!
--
Oscar Picos
Office Autom

It will help if you post the entire SQL statement of the query. The
reason
what I suggested isn't working likely is because of how this expression
is
being used right now.
--

Ken Snell
<MS ACCESS MVP>

Ken: thanks for your help, but unfortunaly it dosn´t works
¿Do you have any other idea?

If I try with a specific value it works, like this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),1,[Forms]![formCriteria]![cmbCity])

But I need all records, in other words, just like the criteria cell is
in
blank.

--
Oscar Picos
Office Autom


:

Try this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),True,[Forms]![formCriteria]![cmbCity])


--

Ken Snell
<MS ACCESS MVP>

I need a Criteria expression that take one of two options about a
combobox's
value in a FormCriteria:

If the value is a City, then the Query only shows records with this
value
in
the IdCity field.

If the combobox is null, then show all records (Without considering
the
IdCity field).

I need an expression (not VBA code), I tried this in the IdCity
field:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),>0,[Forms]![formCriteria]![cmbCity])

But the Query shows 0 rows If the combobox is null.

I will appreciate any help...

Thanks a lot.
And sorry for my english.

 

[Gary Walter]

irrelevant point for most of us, but solution ....

WHERE City = IIF(arg_City IS NULL, City, arg_City);

for the case of one or more records where
[City] is Null, those records will not be returned.

Thanks. The SQL statement makes it easier to see what you're trying to do.
Jamie's solution is the one to use.

--

Ken Snell
<MS ACCESS MVP>

OK, the SQL statement is (Fields & tables are in spanish):

SELECT Clientes.Nombre, Lst_Ciudades.Ciudad
FROM Lst_Ciudades INNER JOIN Clientes ON Lst_Ciudades.IdCd =
Clientes.IdCd
WHERE
(((Lst_Ciudades.IdCd)=IIf(IsNull([Forms]![formCriteria]![Ciudad]),True,[Forms]![formCriteria]![Ciudad])));

Thanks!
--
Oscar Picos
Office Autom

It will help if you post the entire SQL statement of the query. The
reason
what I suggested isn't working likely is because of how this expression
is
being used right now.
--

Ken Snell
<MS ACCESS MVP>


Ken: thanks for your help, but unfortunaly it dosn´t works
¿Do you have any other idea?

If I try with a specific value it works, like this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),1,[Forms]![formCriteria]![cmbCity])

But I need all records, in other words, just like the criteria cell is
in
blank.

--
Oscar Picos
Office Autom


:

Try this:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),True,[Forms]![formCriteria]![cmbCity])


--

Ken Snell
<MS ACCESS MVP>

I need a Criteria expression that take one of two options about a
combobox's
value in a FormCriteria:

If the value is a City, then the Query only shows records with this
value
in
the IdCity field.

If the combobox is null, then show all records (Without considering
the
IdCity field).

I need an expression (not VBA code), I tried this in the IdCity
field:

IIF(IsNull([Forms]![formCriteria]![cmbCity]),>0,[Forms]![formCriteria]![cmbCity])

But the Query shows 0 rows If the combobox is null.

I will appreciate any help...

Thanks a lot.
And sorry for my english.

 

[Gary Walter]

I know you don't like VB specific functions, but
null-to-zero function might look more "elegant"...

{untested}

WHERE
Nz([City],Chr(0)) =
IIF(arg_City IS NULL, Nz([City],Chr(0)), arg_City);

of course, "better"...

WHERE
City = arg_City
OR
arg_City IS NULL;

I probably shouldn't have brought it up --
it really was nitpicking...sorry....

irrelevant point for most of us, but solution ....

WHERE City = IIF(arg_City IS NULL, City, arg_City);

for the case of one or more records where
[City] is Null, those records will not be returned.

In that case, the logic that looks fine in standard SQL e.g.

WHERE COALESCE(City, '{{NONE}}') = COALESCE(arg_City, City, '{{NONE}}');

doesn't look so elegant when translated to Jet SQL e.g.

WHERE IIF(City IS NULL, '{{NONE}}', City) = SWITCH(arg_City IS NULL AND
City
IS NULL, '{{NONE}}', arg_City IS NULL, City, TRUE, arg_City)

Jamie.