The opposite of <> (Not null)


Whats the opposite of using <>

I want to count the number of rows that are blank ?
andyw27Asked:
Who is Participating?
 
Irwin SantosComputer Integration SpecialistCommented:
=
0
 
andyw27Author Commented:
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 ?
0
 
Irwin SantosComputer Integration SpecialistCommented:
http://support.microsoft.com/?kbid=209505

   =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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
puppydogbuddyCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:
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;
---

=Sum(IIf(IsDate([OrderDate]),1,0))
0
 
SidFishesCommented:
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 zero..it can be null or empty

If len(myField) = 0 then
0
 
Donald MaloneyConsultantCommented:
Andy,

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.

Don
0
 
GRayLCommented:
Try this:

=Sum(IIF(nz([date],0)=0,1,0)
0
 
GRayLCommented:
Oops parenetheses:

=Sum(IIF(nz([date],0)=0,1,0))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.