Trying to find Nulls in an IIF statement

Okay. I'm still having problems with a query by form.

I am querying a field (DateComp – type Date) in a table (t_Requests).

On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is Null"
If they select All I want all DateComp records.

For ease of testing I'm using I'm just seeing if I can't it to work using
option value 1 and simply changing the true statement.

Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") This works great, I get 3
expected records.

My problem is when I try to find either only Null or All records.
Like "*" Or Is Null - Gives me all 15 records.
Is Null – Gives me 12 records

When I embed I get no records:
Like IIf([Forms]![1_Main]![Status_Grp]=1,Is Null)
the SQL is: Like IIf([Forms]![1_Main]![Status_Grp]=1,(t_Requests.DateComp)
Like "*" Or (t_Requests.DateComp) Is Null))
Access then converts the query criteria to: Like
IIf([Forms]![1_Main]![Status_Grp]=1,([t_Requests].[DateComp]) Like "*" Or
([t_Requests].[DateComp]) Is Null)

I found MSKB #209261 Showing All Records (Including Null) in a Parameter
Query and created a text control for the DateComp field setting its
visibility to No.

Like IIf([Forms]![1_Main]![Status_Grp]=1,(Like [Forms]![1_Main]![DateComp] &
"*" Or [Forms]![1_Main]![DateComp] Is Null))

The SQL ends up w/this:
(t_Requests.DateComp) Like
IIf([Forms]![1_Main]![Status_Grp]=1,((t_Requests.DateComp) Like
[Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is Null)))

and no results.

But if I the IIF portion of the criteria and just leave it:
(Like [Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)
I get all 15 records.

Lori

Have you tried using something like:

... IsNull(YourField), "do this", "else do this")

Good luck

Jeff Boyce
Access MVP

"Lori" wrote in message
...
Okay. I'm still having problems with a query by form.

I am querying a field (DateComp – type Date) in a table (t_Requests).

On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is
Null"
If they select All I want all DateComp records.

For ease of testing I'm using I'm just seeing if I can't it to work using
option value 1 and simply changing the true statement.

Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") This works great, I get 3
expected records.

My problem is when I try to find either only Null or All records.
Like "*" Or Is Null - Gives me all 15 records.
Is Null – Gives me 12 records

When I embed I get no records:
Like IIf([Forms]![1_Main]![Status_Grp]=1,Is Null)
the SQL is: Like
IIf([Forms]![1_Main]![Status_Grp]=1,(t_Requests.DateComp)
Like "*" Or (t_Requests.DateComp) Is Null))
Access then converts the query criteria to: Like
IIf([Forms]![1_Main]![Status_Grp]=1,([t_Requests].[DateComp]) Like "*" Or
([t_Requests].[DateComp]) Is Null)

I found MSKB #209261 Showing All Records (Including Null) in a Parameter
Query and created a text control for the DateComp field setting its
visibility to No.

Like IIf([Forms]![1_Main]![Status_Grp]=1,(Like [Forms]![1_Main]![DateComp]
&
"*" Or [Forms]![1_Main]![DateComp] Is Null))

The SQL ends up w/this:
(t_Requests.DateComp) Like
IIf([Forms]![1_Main]![Status_Grp]=1,((t_Requests.DateComp) Like
[Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)))

and no results.

But if I the IIF portion of the criteria and just leave it:
(Like [Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)
I get all 15 records.

Jeff~

Thanks, but that will tell me if the Control is Null, since it has a default
value it will never be null.

I'm trying to find all the records in a tabel that are null. As soon as I
embed a criteria that gives x-number of records in the IIF statement and no
records are returned.

~Lori

"Jeff Boyce" wrote:

Lori

Have you tried using something like:

... IsNull(YourField), "do this", "else do this")

Good luck

Jeff Boyce
Access MVP

"Lori" wrote in message
...
Okay. I'm still having problems with a query by form.

I am querying a field (DateComp – type Date) in a table (t_Requests).

On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is
Null"
If they select All I want all DateComp records.

For ease of testing I'm using I'm just seeing if I can't it to work using
option value 1 and simply changing the true statement.

Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") This works great, I get 3
expected records.

My problem is when I try to find either only Null or All records.
Like "*" Or Is Null - Gives me all 15 records.
Is Null – Gives me 12 records

When I embed I get no records:
Like IIf([Forms]![1_Main]![Status_Grp]=1,Is Null)
the SQL is: Like
IIf([Forms]![1_Main]![Status_Grp]=1,(t_Requests.DateComp)
Like "*" Or (t_Requests.DateComp) Is Null))
Access then converts the query criteria to: Like
IIf([Forms]![1_Main]![Status_Grp]=1,([t_Requests].[DateComp]) Like "*" Or
([t_Requests].[DateComp]) Is Null)

I found MSKB #209261 Showing All Records (Including Null) in a Parameter
Query and created a text control for the DateComp field setting its
visibility to No.

Like IIf([Forms]![1_Main]![Status_Grp]=1,(Like [Forms]![1_Main]![DateComp]
&
"*" Or [Forms]![1_Main]![DateComp] Is Null))

The SQL ends up w/this:
(t_Requests.DateComp) Like
IIf([Forms]![1_Main]![Status_Grp]=1,((t_Requests.DateComp) Like
[Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)))

and no results.

But if I the IIF portion of the criteria and just leave it:
(Like [Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)
I get all 15 records.

Lori wrote:
On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is Null"
If they select All I want all DateComp records.

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER);

INSERT INTO Test VALUES (1,1);
INSERT INTO Test VALUES (2, NULL);
INSERT INTO Test VALUES (3,3);
INSERT INTO Test VALUES (4, NULL);
INSERT INTO Test VALUES (5, 5);

CREATE PROCEDURE TestProc
(:status INTEGER = 1)
AS
SELECT key_col, data_col
FROM Test
WHERE
IIF(:status = 1 AND data_col IS NOT NULL, 1,
IIF(:status = 2 AND data_col IS NULL, 1,
IIF(:status = 3, 1, 0)
)
) = 1;

EXEC TestProc;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 1;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 2;
-- returns even numbers (data_col IS NULL)

EXEC TestProc 3;
-- returns all rows

EXEC TestProc 4;
-- returns no rows (parameter value is invalid)

Lori

I'm not sure I understand what you mean by "all the records ... that are
null"...

--
More info, please ...

Jeff Boyce
Access MVP

"Lori" wrote in message
...
Jeff~

Thanks, but that will tell me if the Control is Null, since it has a
default
value it will never be null.

I'm trying to find all the records in a tabel that are null. As soon as I
embed a criteria that gives x-number of records in the IIF statement and
no
records are returned.

~Lori

"Jeff Boyce" wrote:

Lori

Have you tried using something like:

... IsNull(YourField), "do this", "else do this")

Good luck

Jeff Boyce
Access MVP

"Lori" wrote in message
...
Okay. I'm still having problems with a query by form.

I am querying a field (DateComp – type Date) in a table (t_Requests).

On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is
Null"
If they select All I want all DateComp records.

For ease of testing I'm using I'm just seeing if I can't it to work
using
option value 1 and simply changing the true statement.

Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") This works great, I get
3
expected records.

My problem is when I try to find either only Null or All records.
Like "*" Or Is Null - Gives me all 15 records.
Is Null – Gives me 12 records

When I embed I get no records:
Like IIf([Forms]![1_Main]![Status_Grp]=1,Is Null)
the SQL is: Like
IIf([Forms]![1_Main]![Status_Grp]=1,(t_Requests.DateComp)
Like "*" Or (t_Requests.DateComp) Is Null))
Access then converts the query criteria to: Like
IIf([Forms]![1_Main]![Status_Grp]=1,([t_Requests].[DateComp]) Like "*"
Or
([t_Requests].[DateComp]) Is Null)

I found MSKB #209261 Showing All Records (Including Null) in a
Parameter
Query and created a text control for the DateComp field setting its
visibility to No.

Like IIf([Forms]![1_Main]![Status_Grp]=1,(Like
[Forms]![1_Main]![DateComp]
&
"*" Or [Forms]![1_Main]![DateComp] Is Null))

The SQL ends up w/this:
(t_Requests.DateComp) Like
IIf([Forms]![1_Main]![Status_Grp]=1,((t_Requests.DateComp) Like
[Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp] Is
Null)))

and no results.

But if I the IIF portion of the criteria and just leave it:
(Like [Forms]![1_Main]![DateComp] & "*" Or [Forms]![1_Main]![DateComp]
Is
Null)
I get all 15 records.

peregenem

I've have looked at this post off/on over the last week or so and doesn't
make sense.

~Lori

" wrote:


Lori wrote:
On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is Null"
If they select All I want all DateComp records.

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER);

INSERT INTO Test VALUES (1,1);
INSERT INTO Test VALUES (2, NULL);
INSERT INTO Test VALUES (3,3);
INSERT INTO Test VALUES (4, NULL);
INSERT INTO Test VALUES (5, 5);

CREATE PROCEDURE TestProc
(:status INTEGER = 1)
AS
SELECT key_col, data_col
FROM Test
WHERE
IIF(:status = 1 AND data_col IS NOT NULL, 1,
IIF(:status = 2 AND data_col IS NULL, 1,
IIF(:status = 3, 1, 0)
)
) = 1;

EXEC TestProc;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 1;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 2;
-- returns even numbers (data_col IS NULL)

EXEC TestProc 3;
-- returns all rows

EXEC TestProc 4;
-- returns no rows (parameter value is invalid)

Lori,

It does make sense if you are used to T-SQL and probably it makes sense if you
are using an Access PROJECT (.adp/.ade). It doesn't make a lot of sense if you
are using a standard Access database (DAO and Jet (.mdb/.mde)).

Based on your original post, I might try a query like the following.

SELECT *
FROM T_Requests
WHERE t_Requests.DateComp Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") OR
(t_Requests.DateComp IS Null AND [Forms]![1_Main]![Status_Grp]=2) OR
[Forms]![1_Main]![Status_Grp]=3

Be aware that Access will rearrange all of this when you save the query and
reopen it. It should still work unless you have quite a bit of additional criteria.

If you want an explanation of the SQL posted by peregenem, post back and if I
have the time perhaps I can explain it. Or perhaps peregenem can give you a
fuller explanation of what was proposed as a solution.

Lori wrote:

peregenem

I've have looked at this post off/on over the last week or so and doesn't
make sense.

~Lori

" wrote:


Lori wrote:
On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is Null"
If they select All I want all DateComp records.

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER);

INSERT INTO Test VALUES (1,1);
INSERT INTO Test VALUES (2, NULL);
INSERT INTO Test VALUES (3,3);
INSERT INTO Test VALUES (4, NULL);
INSERT INTO Test VALUES (5, 5);

CREATE PROCEDURE TestProc
(:status INTEGER = 1)
AS
SELECT key_col, data_col
FROM Test
WHERE
IIF(:status = 1 AND data_col IS NOT NULL, 1,
IIF(:status = 2 AND data_col IS NULL, 1,
IIF(:status = 3, 1, 0)
)
) = 1;

EXEC TestProc;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 1;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 2;
-- returns even numbers (data_col IS NULL)

EXEC TestProc 3;
-- returns all rows

EXEC TestProc 4;
-- returns no rows (parameter value is invalid)

John~

I grant the wish of you choice! It worked wonderfully!!

~Lori

"John Spencer (MVP)" wrote:

Lori,

It does make sense if you are used to T-SQL and probably it makes sense if you
are using an Access PROJECT (.adp/.ade). It doesn't make a lot of sense if you
are using a standard Access database (DAO and Jet (.mdb/.mde)).

Based on your original post, I might try a query like the following.

SELECT *
FROM T_Requests
WHERE t_Requests.DateComp Like IIf([Forms]![1_Main]![Status_Grp]=1,"*") OR
(t_Requests.DateComp IS Null AND [Forms]![1_Main]![Status_Grp]=2) OR
[Forms]![1_Main]![Status_Grp]=3

Be aware that Access will rearrange all of this when you save the query and
reopen it. It should still work unless you have quite a bit of additional criteria.

If you want an explanation of the SQL posted by peregenem, post back and if I
have the time perhaps I can explain it. Or perhaps peregenem can give you a
fuller explanation of what was proposed as a solution.

Lori wrote:

peregenem

I've have looked at this post off/on over the last week or so and doesn't
make sense.

~Lori

" wrote:


Lori wrote:
On form (1_Main) there's an option group (Status_Grp): Closed (value
1/Default), Open (value 2), All (value 3).

If they select Closed I want all DateComp records with values, ie "*"
If they select Open I want all DateComp records that are null, ie "Is Null"
If they select All I want all DateComp records.

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER);

INSERT INTO Test VALUES (1,1);
INSERT INTO Test VALUES (2, NULL);
INSERT INTO Test VALUES (3,3);
INSERT INTO Test VALUES (4, NULL);
INSERT INTO Test VALUES (5, 5);

CREATE PROCEDURE TestProc
(:status INTEGER = 1)
AS
SELECT key_col, data_col
FROM Test
WHERE
IIF(:status = 1 AND data_col IS NOT NULL, 1,
IIF(:status = 2 AND data_col IS NULL, 1,
IIF(:status = 3, 1, 0)
)
) = 1;

EXEC TestProc;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 1;
-- returns odd numbers (data_col IS NOT NULL)

EXEC TestProc 2;
-- returns even numbers (data_col IS NULL)

EXEC TestProc 3;
-- returns all rows

EXEC TestProc 4;
-- returns no rows (parameter value is invalid)

John Spencer (MVP) wrote:

John, I've have looked at this post off/on over the last day or so and
I'm not sure quite what to make of it.

It does make sense if you are used to T-SQL

Are you implying my code is T-SQL? It is not. If you tried to run it in
SQL Server or MSDE it would error.

For parameters, T-SQL does not support the standard SQL syntax of a
colon prefix (e.g. :status), instead you get a Hobson's Choice of @
(e.g. @status). T-SQL does support the standard SQL CASE syntax,
whereas Jet does not, so we have to use IIF, SWITCH etc.

The equivalent T-SQL script is as follows:

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER)
INSERT INTO Test VALUES (1,1)
INSERT INTO Test VALUES (2, NULL)
INSERT INTO Test VALUES (3,3)
INSERT INTO Test VALUES (4, NULL)
INSERT INTO Test VALUES (5, 5)
GO

CREATE PROCEDURE TestProc
(@status INTEGER = 1)
AS
SELECT key_col, data_col
FROM Test
WHERE
CASE
WHEN @status = 1 AND data_col IS NOT NULL THEN 1
WHEN @status = 2 AND data_col IS NULL THEN 1
WHEN @status = 3 THEN 1
ELSE 0
END = 1
GO

Or are you implying that only people who use T-SQL are courteous enough
to post SQL DDL (data declaration language) code and sample data AS
INSERT INTO SQL DML (data modification language) statements so that any
reader may easily create the tables and data required to test the demo
or proposed solution? It is netiquette in the SQL Server groups for the
OP to this, rather than make the person responding to all the hard
work. True, DDL is rarely seen in the Access groups and sample data is
normally posted as fixed length text. I post DDL for the schema and DML
for the sample data because I think it's worth the effort to set a good
example.

In this case, the OP's SQL was overly complicated for the simple point
required; also it relies on front end elements which I would find
extremely difficult to recreate. So I invented my own test using a new
table with five rows of data and a new procedure ('parameter Query'
object), the WHERE clause for which is key to this solution.

It doesn't make a lot of sense if you
are using a standard Access database (DAO and Jet (.mdb/.mde)).

I don't know by what you implying by using the word 'standard' here.

The SQL code I originally posted is Jet 4 SQL syntax. Some of the
syntax I have employed can only be used in the Access UI if you are
using the .mdb/.mde file in Access2003 and has been set to 'ANSI' SQL
query mode (see
http://office.microsoft.com/en-us/as...704831033.aspx),
otherwise the OLE DB provider for Jet 4 must be used e.g. via ADO. When
can we assume that Access2003 is a 'standard' version?

Jet 4, ADO and the OLE DB have been around since Access2000 but can we
assume 'standard' means Access2000 and above? If Access97 is still the
most widely used version and we must consider that to be the 'standard'
version, must we avoid VBA6 syntax because Access97 doesn't support it?

You mention DAO as being 'standard' but since Access2000 hasn't ADO
been the default data access technology referenced by Access? (For the
record, I prefer the Access2003 viewpoint i.e. that DAO and ADO are
both 'standard' and equally valid.)

As I said, John, for me your post raises more questions than answers.

To the OP: I'll assume you have .mdb file open in Access version
Access97 (Jet 3) or above. (I'm working from distant memory here so
bear with me.)

Open a new blank Query object, cancel any dialogs inviting you to add
tables. When you can, navigate to the SQL view window. Paste in the
following Jet 3 SQL code:

CREATE TABLE Test (
key_col INTEGER NOT NULL,
data_col INTEGER)

Execute the SQL code, and thereby create the test table, by pressing
the toolbar button with an exclamation mark (!) on it.

Now clear the previous code and paste in the following:

INSERT INTO Test VALUES (1,1)

Execute again and say Yes or OK to the warning about creating a new
row. Repeat individually for each of these four INSERT INTO statements:

INSERT INTO Test VALUES (2, NULL)
INSERT INTO Test VALUES (3,3)
INSERT INTO Test VALUES (4, NULL)
INSERT INTO Test VALUES (5, 5)

To create the 'parameter Query' object, paste in the following:

PARAMETERS :status INTEGER = 1;
SELECT key_col, data_col
FROM Test
WHERE
IIF(:status = 1 AND data_col IS NOT NULL, 1,
IIF(:status = 2 AND data_col IS NULL, 1,
IIF(:status = 3, 1, 0)
)
) = 1;

Rather than executing, save the query object using the name TestProc
and close it.

To test, try just selecting from the Query's virtual table e.g. put
this in the SQL view of another (empty) Query object:

SELECT * FROM TestProc

and type in the :status parameter values when prompted. Try the values
1 to 4. Using 1 returns odd numbers (data_col IS NOT NULL). Using 2
returns even numbers (data_col IS NULL). Using 3 returns all rows.
Using 4 returns no rows (parameter value is invalid).

Now take a look at the 'parameter Query' object's WHERE clause. For
your solution, substitute your front end elements
(Forms]![1_Main]![Status_Grp] etc) for my parameters and my column
names with your column names.

To the OP: I'll assume you have .mdb file open in Access version
Access97 (Jet 3) or above. (I'm working from distant memory here so
bear with me.)

Open a new blank Query object, cancel any dialogs inviting you to add
tables. When you can, navigate to the SQL view window. Paste in the
following Jet 3 SQL code:

Why should the OP do any of that? She already told you that she had no idea
what you were talking about AND she got what she needed with John's example.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html