counting Null fields

[fishqqq]

i have a query that I'm trying to calculate NULL fields for but am
having problems.

Sold:[sold] (criteria is set to <>NULL)
Unsold:[sold] (criteria is set to IS NULL)
Date:[record date]

i have the same field on the query twice. The idea is the records that
are sold will have a value in them and the records that are unsold
will have no value (null). I'm trying to total both these fields to
show me how many records represent sold and unsold for a particular
date. The "count" option won't count the null records though. is there
a better way to do this?

Tks
STeve

 

[John W. Vinson]

i have a query that I'm trying to calculate NULL fields for but am
having problems.

Sold:[sold] (criteria is set to <>NULL)
Unsold:[sold] (criteria is set to IS NULL)
Date:[record date]

i have the same field on the query twice. The idea is the records that
are sold will have a value in them and the records that are unsold
will have no value (null). I'm trying to total both these fields to
show me how many records represent sold and unsold for a particular
date. The "count" option won't count the null records though. is there
a better way to do this?

Tks
STeve

A couple of things here: first off, nothing is equal to NULL, nor is anything
*unequal* to NULL. NULL means "undefined, unknown, unspecified" - so any
comparison (=, <>, >, <, etc.) with NULL doesn't return either true or false,
it returns NULL ("I don't know if it's equal or not, because it's undefined!")

Try two calculated fields (in vacant FIELD cells, not in the criteria line)L

Sold:IIF(IsNull([sold]), 0, 1)
Unsold: IIF(IsNull([sold]), 1, 0)

The VBA function IsNull returns either TRUE or FALSE. The IIF will return a 1
or 0 appropriately, and you can use a totals query to sum (not count!) the
1's.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

Your criteria for sold should be Is NOT Null . <> Null should fail to return
records.

If you want to count the Nulls then you have to use an expression like

Field: UnSold: IIF([TableName].[Sold] is Null,1,Null)
Total: Count

Field: Sold
Total: Count

You would not apply criteria at all against the field Sold field.

Next time post the SQL of the query you are using and someone can modify it to
give you what you need. Plus it is much easier for us to have the information
on exactly what you are doing in the query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[fishqqq]

i have a query that I'm trying to calculate NULL fields for but am
having problems.

Sold:[sold]  (criteria is set to <>NULL)
Unsold:[sold] (criteria is set to IS NULL)
Date:[record date]

i have the same field on the query twice. The idea is the records that
are sold will have a value in them and the records that are unsold
will have no value (null). I'm trying to total both these fields to
show me how many records represent sold and unsold for a particular
date. The "count" option won't count the null records though. is there
a better way to do this?

Tks
STeve

A couple of things here: first off, nothing is equal to NULL, nor is anything
*unequal* to NULL. NULL means "undefined, unknown, unspecified" - so any
comparison (=, <>, >, <, etc.) with NULL doesn't return either true or false,
it returns NULL ("I don't know if it's equal or not, because it's undefined!")

Try two calculated fields (in vacant FIELD cells, not in the criteria line)L

Sold:IIF(IsNull([sold]), 0, 1)
Unsold: IIF(IsNull([sold]), 1, 0)

The VBA function IsNull returns either TRUE or FALSE. The IIF will returna 1
or 0 appropriately, and you can use a totals query to sum (not count!) the
1's.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

this worked very well thank you