The opposite of <> (Not null)

Posted on 2006-04-22
Last Modified: 2012-08-13

Whats the opposite of using <>

I want to count the number of rows that are blank ?
Question by:andyw27
    LVL 30

    Accepted Solution


    Author Comment

    I haver this line currently:

    =Sum(IIf([date]= <>,1,0))

    I've tried replacing it with both:

    =Sum(IIf([date]= ,1,0))

    =Sum(IIf([date]= =,1,0))

    Does'nt work?  Am I taking your instruction too literally ?
    LVL 30

    Expert Comment


       =Sum(IIF([YesNoField],1,0))        Yes
       =Sum(IIF([YesNoField],0,1))        No
       =Sum(IIF(Not[YesNoField],1,0))     No
       =Sum(IIF(IsNull[YesNoField],1,0))  Null

    the above is a good reference
    LVL 38

    Assisted Solution

    Yes, the syntax used depends on the contex:
    For null values these syntaxes have been used in different situations:
                = Null, Is Null; IsNull; nz

    For not null:
                <> Null;Not IsNull; Is Not Null
    LVL 119

    Expert Comment

    by:Rey Obrero
    if you are checking a date field you can use  Isdate([DateField]) to count the non empty field

    SELECT Sum(IIf(IsDate([OrderDate]),1,0)) AS Expr1
    FROM tblOrders;

    LVL 36

    Assisted Solution

    also remember that "blank" can be NOt Null. An empty string "" is Not Null

    YOu can test for empty with Not IsEmpty or <> ""

    you can build a statement like

    if myField Not isNull and Not isEmpty

    but a better way is to simply evaluate the length of the field. if it is can be null or empty

    If len(myField) = 0 then
    LVL 11

    Expert Comment


    What your expression "=Sum(IIf([date]= <>,1,0))"

    says  is:    Sum,  if field Date is equal and not equal then use numeric 1  else use 0.

    It will always be 0 because the compare cannot be both equal and not equal.

    Capricorns        =Sum(IIf(IsDate([OrderDate]),1,0))
    says   Sum,  if field Date is a real date then use numeric 1  else use 0.

    If you are comparing the field, in a query to a table/field called date then

    sum (IIf ([TableField] = [date] ,1,0))
    says  sum, if the table field is equal to the field date then use numeric 1  else use 0.

    LVL 44

    Expert Comment

    Try this:

    LVL 44

    Assisted Solution

    Oops parenetheses:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now