Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-03-2007, 12:49 PM   #1
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
Criteria syntax problem

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!

Squidinker is offline   Reply With Quote
Old 07-03-2007, 10:41 PM   #2
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
it's a bit hard to see what you're after, but how about:

Nz([Forms]![Available Players]![FilterOwned]),"Is Null")
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 07-04-2007, 01:00 AM   #3
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
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).

__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 07-04-2007, 03:07 AM   #4
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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
Squidinker is offline   Reply With Quote
Old 07-04-2007, 09:32 AM   #5
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
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:

Quote:
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.
__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 07-04-2007, 10:21 AM   #6
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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.
Squidinker is offline   Reply With Quote
Old 07-04-2007, 10:27 AM   #7
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
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

__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by wazz; 07-04-2007 at 10:47 AM.
wazz is offline   Reply With Quote
Old 07-04-2007, 12:04 PM   #8
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
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...

Squidinker is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup syntax problem cronid Queries 2 06-07-2006 12:34 PM
Problem with more than TWO criteria seabass Queries 4 04-11-2006 02:38 PM
Problem with using criteria from form in crosstab query adamlaing Queries 5 11-07-2005 02:11 AM
multiple "OR" criteria Problem smercer Queries 10 08-10-2004 07:05 AM
Query criteria can either have a value or not problem Angielah Queries 1 06-16-2004 06:02 AM




All times are GMT -8. The time now is 07:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World