counting Null fields

  • Thread starter Thread starter fishqqq
  • Start date Start date
F

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
 
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
Click to expand...

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
 
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
 
i have a query that I'm trying to calculate NULL fields for but am
having problems.
Click to expand...
Sold:[sold]  (criteria is set to <>NULL)
Unsold:[sold] (criteria is set to IS NULL)
Date:[record date]
Click to expand...
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?
Click to expand...
Tks
STeve
Click to expand...

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
Click to expand...

this worked very well thank you
 
Back
Top