Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The opposite of <> (Not null)

Posted on 2006-04-22
11
Medium Priority
?
620 Views
Last Modified: 2012-08-13

Whats the opposite of using <>

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

Accepted Solution

by:
Irwin Santos earned 500 total points
ID: 16514472
=
0
 

Author Comment

by:andyw27
ID: 16514483
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
 
LVL 30

Expert Comment

by:Irwin Santos
ID: 16514536
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 500 total points
ID: 16514556
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16514886
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
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 500 total points
ID: 16514919
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
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 16515188
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16516089
Try this:

=Sum(IIF(nz([date],0)=0,1,0)
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 500 total points
ID: 16516091
Oops parenetheses:

=Sum(IIF(nz([date],0)=0,1,0))
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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