Learn how to a build a cloud-first strategyRegister Now

x
Solved

# The opposite of <> (Not null)

Posted on 2006-04-22
Medium Priority
620 Views

Whats the opposite of using <>

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

LVL 30

Accepted Solution

Irwin Santos earned 500 total points
ID: 16514472
=
0

Author Comment

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

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

LVL 38

Assisted Solution

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

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

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

ID: 16515188
Andy,

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

ID: 16516089
Try this:

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

LVL 44

Assisted Solution

GRayL earned 500 total points
ID: 16516091
Oops parenetheses:

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

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month20 days, 20 hours left to enroll