Criteria syntax problem (1 Viewer)

Squidinker

Registered User.
Local time
Today, 14:03
Joined
Jun 28, 2007
Messages
11
Hi,

Should be a very quick question for someone with more experience than me (i.e. everyone on here):

What is the correct syntax in an IIf function that allows "Is Null" to be the criteria if the logic is met?
I have an IIf statement as a field's criteria in a query and one of the results of the IIf requires only those records that have a Null in that field.

I have tried:

IIf(([Forms]![Available Players]![FilterOwned])=0,"","Is Null")
..."Expression typed incorrectly"

IIf(([Forms]![Available Players]![FilterOwned])=0,"",Is Null) ...runs without error but does not filter, indeed it shows no records at all.
When I go back into the query design the criteria has changed to:
IIf(([Forms]![Available Players]![FilterOwned])=0,"",([Players Auction].[Owner]) Is Null)

Please help!
 

wazz

Super Moderator
Local time
Today, 22:03
Joined
Jun 29, 2004
Messages
1,711
it's a bit hard to see what you're after, but how about:

Nz([Forms]![Available Players]![FilterOwned]),"Is Null")
 

Moniker

VBA Pro
Local time
Today, 09:03
Joined
Dec 21, 2006
Messages
1,567
I think you have the wrong idea for what Is Null does. Unless you're trying to return the text "Is Null", it's all backward. If you're trying to determine if something is null and set text, use Nz, like this:

YourFieldName = Switch(Nz([Forms]![Available Players]![FilterOwned]),"")="","Is Null")

I think the issue you are having is thinking that a zero-length string and a NULL string are equivalent. They aren't. This "" is not equal to NULL. A NULL value is nothing, and a Zero-Length string is something. These two are not equivalent:

If IsNull(SomeVariable)

---

If SomeVariable = ""

The NULL value is not a yes, a no, a zero-length string, or anything else. It's nothing. You cannot use comparisons in NULLs. None of these will work as expected:

If SomeVariable <> Null

---

It SomeVariable = Null

etc.

That's why IsNull exists. It's a special case. To help get this straight in your head, a zero-length string is something that at some point had something written to it, be it any sort of data or "" or anything else. A NULL string is a created variable that's never had anything written to it. Once you write "" to a variable, it's no longer NULL. (You can revert to a NULL by setting SomeVariable = Null since Null is a keyword.)

I hope that makes sense. It's a weirdo difference, but here's one final attempt at clarifying it. You write the number 1 on a piece of paper. That paper is no longer NULL. You no longer need that number, so you set it to "", but keep the same piece of paper. It's now a zero-length string. To treat that same piece of paper as if you never wrote the number 1 on it, you'd need to throw it out and get a new piece. The throwing it out part is the equivalent of writing the Null keyword to it. The erasing it part is the equivalent of deleting it (setting it to a zero-length string).
 

Squidinker

Registered User.
Local time
Today, 14:03
Joined
Jun 28, 2007
Messages
11
Thanks a lot for the help guys...

However I think I did a pretty poor job explaining what I am trying to do.

I have a query that pulls from several tables. The result of this table is used in a subform as a list of items which primarily will be used for visual analysis.
One of the fields, [Owner] is empty until a particular item is used in an auction and then the name of the winner of that particular auction populates this field (there are more items than there will be auctions so not all items will have this field populated).

This subform - used for analysis - will show all records, who owns them (if anyone) and several other parameters.
If I put "Is Null" (without the quotes) as a criteria for the [Owner] field, the query returns only those items which have no owner; this is perfect. I now have a list of items that are available.

However I would like to toggle between the available items and all items including available and non-available.

Hence the criterion's logic I am looking for is, "If the checkbox 'Filter Unavailable' is off, then leave the criteria empty. Else if the filter is on, make the criteria "Is Null" - show only those items with nothing in this field".

I have done this with other criteria in other fields using this logic but where the criteria required (on tick of the tickbox) is a simple letter to be searched for:

IIf(([Forms]![Available Players]![GCheck])=0,"","G")

Works perfectly since the filter I want is the letter G and not a command, but I need a version of this with Is Null instead of "G".

I hope this explains it a little better :confused:
 

Moniker

VBA Pro
Local time
Today, 09:03
Joined
Dec 21, 2006
Messages
1,567
Okay, that's just a usage issue. Your original:

IIf(([Forms]![Available Players]![FilterOwned])=0,"",Is Null)

is not in the right format. Look in help for the command IsNull (no space) and you'll get this:

Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

There's an example or two included as well. That should point you in the right direction.
 

Squidinker

Registered User.
Local time
Today, 14:03
Joined
Jun 28, 2007
Messages
11
I'm not sure I see how that is going to help. I don't want to use Is Null or IsNull as an expression; I only want it returned if a given condition is met (the form tickbox is checked).
IsNull returns a true or false where all I want to do is to filter out those records that are NOT Null.
What I really need is a way of toggling between Is Null as a criteria and no criteria for a given field.
 

wazz

Super Moderator
Local time
Today, 22:03
Joined
Jun 29, 2004
Messages
1,711
it's an interesting approach...i can't get it to work yet...although i got half-way with your last example: IIf(([Forms]![Available Players]![GCheck])=0,"","G")

i'll just throw this out there in case you haven't done this before:
i usually create two (or however many) queries and change the record source of the form.
Code:
Private Sub chkFilterForNulls_AfterUpdate()

    With Me
        If .RecordSource = "qryMyFirstQueryWithoutCriteria" Then
            .RecordSource = "qryMySecondQueryWithCriteria"
        Else
            .RecordSource = "qryMyFirstQueryWithoutCriteria"
        End If
    End With

End Sub
 
Last edited:

Squidinker

Registered User.
Local time
Today, 14:03
Joined
Jun 28, 2007
Messages
11
Thanks for the reply Wazz - worst case, then two queries will work (I hadn't really considered that).
To be honest, I haven't given alternative solutions much thought since I felt the solution would be exceedingly simple.
After all, all I want is to use a checkbox to alternate between and empty query field criteria and Is Null as the criteria. I figured the problem simply stemmed from the difference between using simple quoted text as a criteria (in my example you quoted) and an actual argument (Is Null).
I didn't expect the solution to be so reclusive!

I have so much more to do here I might leave this for now and move on with another part. Thanks for the efforts though guys, I'll revisit this later if no solution is forthcoming.

Edit: I suppose an alternative avenue would be if there is an alternate way of filtering out records which are not null - i.e. showing only those records where a given field is empty...
 

Users who are viewing this thread

Top Bottom