Query to Count "0" in Row

[antmorano]

I am trying to create an expression in a query that says if
CONTRIBUTION BEGIN year is less then 2006 and the AUTE2 = 00/00/00
then count the number of "0" in the rown and multiply that by 58.

This is what I had thought up so far, but I know it isn't right.

CONTRIBUTION DUE: IIF([CONTRIBUTION BEGIN], 'yyyy' <2006, AND AUTE2 =
00/00/00, Count "0" And Multiply by 58

Any input is greatly appreciated.

-Anthony Morano

 

[Jeff Boyce]

What kind of data type is being stored in your [AUTE2] field? If that is
supposed to be a date, I don't think Access considers #00/00/00# to be a
valid date. Note the use of the "#" symbol before and after a "date". Use
quotes around text, "#"'s around dates, and nothing around numbers.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

What type of field is AUTE2? If it is a date field then it can't be
00/00/00, so the assumption is that the field must be a text field.

PERHAPS, what you want is the following.

COUNT(IIF([Contribution Begin] <#2006-01-01# AND AUTE2 = "00/00/00" AND
SomeField = 0)) * 58

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[antmorano]

Thanks for responding all. I have just set the AUTE2 field to date/
time and as short date for the input mask. Every field which should
be a date field is now formatted to be one. John- I'm having a little
trouble understanding your logic with the AND SomeField = 0)) *58? I
appreciate all the help.

-Anthony Morano

 

[antmorano]

Also, there is no longer 00/00/00. When I switched the format of the
field to dat/time, it deleted all of those fields and thus they are
now empty. I'm assuming that it would now be "Is Null" in the
formula?

 

[antmorano]

Also John- I am trying what you gave me and I have gotten farther
except for the fact that you have named "SomeField". The problem is
that it is a range of columns, AUPS1-AUPS12 (for the 12 months of the
year). How can I create a range to search for the 0's?

-Anthony Morano

 

[John Spencer]

OUCH! OUCH! OUCH! This structure is making your life tough.

You can try something like the following.

CONTRIBUTION DUE:
COUNT(IIF([Contribution Begin] <#2006-01-01# AND AUTE2 Is Null,
Abs(AUPS1 = 0 + AUPS2=0 + AUPS3=0 + AUPS4=0 + ... + AUPS12=0)
, Null) * 58

IF any AUPS can be null then you have to decide whether you want to
treat the Nulls as zeroes or ignore them. To treat nulls as zero you
will need to use either an IIF statement or the NZ function.

IIF(AUPS1 Is Null, 0, AUPS1)=0 + IIF(AUPS2 is Null, 0,AUPS2)=0 + ...

If you wish to ignore the nulls then replace the zero with any non-Zero
value.

IIF(AUPS1 Is Null, 22, AUPS1)=0

Have fun
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

John's OUCH is for YOUR pain.

It sounds like you've created a spreadsheeet (one column per month). You
won't get much out of Access' relationally-oriented features and functions
if you feed it 'sheet data.

If the rows of data you have include a 'value-for-a-month', use just two
fields, a [Value], and a [WhichMonth].

And you won't need to start all over again next year when another January
rolls around if you use [ValueDate] instead of [WhichMonth].

If you truly only need and care about the Month-related values, consider
using a spreadsheet instead of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

How can I create a range to search for the 0's?
You can not. You have you table set up like a spreadsheet.
But you can use this ---
NZ([AUPS1],0) + NZ([AUPS2],0) + ....... NZ([AUPS11],0) + NZ([AUPS12],0)

 

[Guest]

What kind of field is AUTE2? A Datetime field cannot have 00/00/00.
What are the field names that the zeros to be counted are in?

This is assuming that AUTE2 is a text field and [CONTRIBUTION BEGIN] is a
Datetime field ---
CONTRIBUTION DUE: IIF(Year([CONTRIBUTION BEGIN]) <2006 AND AUTE2 =
"00/00/00",
----- missing information where zeros are to be found ----
* 58

Can not build it without the rest of the puzzle.