Trying to find Nulls in an IIF statement

[Lori]

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

Lori

Have you tried using something like:

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

Good luck

Jeff Boyce
<Access MVP>

 

[Lori]

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

Lori

Have you tried using something like:

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

Good luck

Jeff Boyce
<Access MVP>

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]

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
IIFstatus = 1 AND data_col IS NOT NULL, 1,
IIFstatus = 2 AND data_col IS NULL, 1,
IIFstatus = 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)

 

[Jeff Boyce]

Lori

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

--
More info, please ...

Jeff Boyce
<Access MVP>

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

Lori

Have you tried using something like:

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

Good luck

Jeff Boyce
<Access MVP>

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]

peregenem

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

~Lori

 

[John Spencer (MVP)]

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]

John~

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

~Lori

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.

peregenem

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

~Lori

 

[peregenem]

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/assistance/HP030704831033.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
IIFstatus = 1 AND data_col IS NOT NULL, 1,
IIFstatus = 2 AND data_col IS NULL, 1,
IIFstatus = 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.

 

[Lynn Trapp]

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/conradsystems/accessjunkie.html

 

[peregenem]

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.

When they said they'd "looked at my post off/on over the last week or
so" I got the impression they were open minded to learning something
new. Also, John Spencer suggested I give a fuller explanation. I think
once the reader can get the code up and running it speaks for itself: I
can't make it any easier than a simple, working example.

If the OP is no longer reading this, perhaps others are. Call me an
optimist

 

[Lynn Trapp]

When they said they'd "looked at my post off/on over the last week or
so" I got the impression they were open minded to learning something
new. Also, John Spencer suggested I give a fuller explanation. I think
once the reader can get the code up and running it speaks for itself: I
can't make it any easier than a simple, working example.

Wouldn't it be much simpler to just create the table using the Access
interface and inserting the records by hand -- since you only entered 5
records? The OP could have done that a lot faster than it would take her to
write the SQL statements. Sure, she could copy and paste your code faster
but that's not going to help her the next time she has to do something
similar.

If the OP is no longer reading this, perhaps others are. Call me an
optimist

Being an optimist is good and I'm sure you will help a few people, but you
will definitely have a small audience for doing that. I write SQL and SQL
Plus code like that hundreds of time every day. However, the vast majority
of people here don't want to have anything to do with that. They just want
to get their job done the easiest way possible.

--
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/conradsystems/accessjunkie.html

 

[peregenem]

Wouldn't it be much simpler to just create the table using the Access
interface and inserting the records by hand

Having done it both ways, I can tell you that posting the SQL code
(CREATE TABLE and INSERT INTOs) was a lot easier and quicker to write
than the post describing the steps required to do the same using the
Access UI. You suggest 'she could copy and paste your code faster'.
Surely this is a 'win win' situation, so I've missed your point. [Is
the OP a 'she'? I guess you've never been mistaken for a 'she'

The OP could have done that a lot faster than it would take her to
write the SQL statements.

I find it faster to write the SQL. I gave up on the UI years ago.

that's not going to help her the next time she has to do something
similar.

I couldn't disagree more. There is a secondary message in my post: that
DDL, test data and expected results means quicker and better experience
for everyone. Better for the potential respondent because they can more
easily recreate the OP's situation and would be more inclined to help.
Better for the OP because the respondent has a better chance at getting
a solution that actually works for the them (how many times have you
posted something only for the OP to come back with, 'I suppose I should
have mentioned ...' and thus change the spec?) Better for the casual
reader because they can follow everything more effectively. DDL is good
for everyone because it is explicit, exacting and, to a large extent,
portable to other platforms (transferrable skills).

However, the vast majority
of people here don't want to have anything to do with [SQL code]

You seem to be saying SQL code is better but not to be recommended
because it is not popular. I hope I have misinterpreted you because I'd
find this a very disappointing position to take.

Comfort zones should be challenged. My way requires some thought and
effort, then maybe the reader will learn something. There will always
be the kind of user who wants a quick fix, a ready-rolled solution.
Surely it is *this* kind of answer is 'not going to help her the next
time she has to do something similar' but they have not learned
anything other than to follow instructions.

I am in no doubt I'm taking the 'path less travelled' but feel that
doing so may help raise standards. I don't mean to shove anything down
throats, I just want to set a good example.