Two Criteria

[Guest]

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])

 

[Guest]

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.

 

[Jamie Collins]

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)

The example uses Northwind and ANSI-92 Query Mode:

CREATE PROCEDURE GetCustomers (
arg_City NVARCHAR(15) = NULL
)
AS
SELECT CustomerID, CompanyName, City
FROM Customers
WHERE City = IIF(arg_City IS NULL, City, arg_City);

Jamie.

--

 

[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.

 

[Jamie Collins]

I know you don't like VB specific functions

I have no problem with the VBA5 scalar functions because they have been
implemented in the Jet 4.0 DLLs ;-) I supposed in the context of SQL we
should call them 'expressions'. I use the word 'implemented' because
some of the Jet functions seem to work differently to their VBA
equivalents e.g. IIF() in Jet appears not to evaluate both true and
false conditions as it does in VBA. And I use the term 'seem to'
because there is no Jet specification to tell the designer's intention.

If you imbed into the SQL definition of a Jet database object (e.g. a
VIEW or Query) a VBA6 function or something specific to a front end
application (Access object model, UDF in a module, etc) then it would
require the Access user interface to work, which would be a little
shorted sighted at least (although this will become increasingly
irrelevant if the Access 2007 format and features have good take up).

It's a shame the Jet team never got around to implementing the VBA6
functions into Jet; Replace$() and StrReverse$() are particularly
missed. The principle seems to have been that only scalar expressions
(as distinct from set functions or 'aggregates') present in VBA can be
implemented in Jet, which is a shame because COALESCE() and NULLIF()
from the SQL standard would have be extremely useful too. I guess with
ACE (Access 2007 engine) the argument is now that the best place to
implement functions is the Access object model, where NZ() has existed
for considerable time.

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);

Using Chr(0) makes porting to another SQL product unnecessarily hard -
why not use a placeholder in single quotes? It was IMO improve
readability too. [That said, IIRC I used CHR(0) myself recently because
the empty string does not render well as '' in a newsgroup post.]

I used '{{NONE}}' because that's what I would have used as the DEFAULT
when I made the column NOT NULL. For me, null city makes no sense; I
see more value in using placeholders for 'not known', 'does not apply'
and 'none' respectively, with double curly brackets to indicate that
the values are placeholders. I reserve NULL as a placeholder for a
value I know will be coming but cannot be known at present e.g. a
star_date and end_date pair in a valid-time state ('history') table
where a null value for end_date indicates the current period (i.e.
there is no end date because it hasn't ended...yet).

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

You should know I appreciate nitpicking <g>.

Jamie.

--