Count cell with date-entrie, not include empty cells

  • Thread starter Thread starter Twisty
  • Start date Start date
T

Twisty

Im a report I have several columns. Two of them are for dates.

I need to count how many times any date appears in each column.

I have tried =Count([dates]), but it does no work the way I need.


This is what comes up:
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009
01.07.2009 01.07.2009
01.07.2009
01.07.2009 01.07.2009
8 8 (should be 6)


Thanks in advance for help :-)
 
Try the following

=Count(IIF(Len(Column2Field &"")>0,1,Null))

If that does not work and the control for Column2 is not bound to a field in a
query, then post the control source for the column2 control. Or if you are
controlling the value that shows using VBA, post that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I am afraid this didnt work.

Maybe someone can tell how to set the controlsource in a textbox to a
spesific column, and then tell me what formula to write, and where to write
it, for counting how many posts in the column that has a date(any date)




John Spencer MVP said:
Try the following

=Count(IIF(Len(Column2Field &"")>0,1,Null))

If that does not work and the control for Column2 is not bound to a field in a
query, then post the control source for the column2 control. Or if you are
controlling the value that shows using VBA, post that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Im a report I have several columns. Two of them are for dates.

I need to count how many times any date appears in each column.

I have tried =Count([dates]), but it does no work the way I need.


This is what comes up:
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009 01.07.2009
01.07.2009
01.07.2009 01.07.2009
01.07.2009
01.07.2009 01.07.2009
8 8 (should be 6)


Thanks in advance for help :-)
Click to expand...
Click to expand...
 
What does "I am afraid this didnt work." mean? Did you get an error, the
wrong results, no results, an explosion?

=Count(IIF(Len([Column2Field] & "")>0,1,Null))

That expression assumes that you have a field named Column2Field. Did you
replace it with the name of a field in the report's record source?

Is the control in a group or report footer?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I changed tfe expression to be:
=Count(IIF(Len([SLUTTKONTROLL UTFØRT] & "")>0,1,Null))
Click to expand...

SLUTTKONTROLL UTFØRT is the name of the column.

A error comes up and says: "The expression you entered has an invalid number"

You ask:
"Is the control in a group or report footer?"
If you mean where i have placed the textbox, which I want to count posts, I
have placed it in the report footer.

I
John Spencer said:
What does "I am afraid this didnt work." mean? Did you get an error, the
wrong results, no results, an explosion?

=Count(IIF(Len([Column2Field] & "")>0,1,Null))

That expression assumes that you have a field named Column2Field. Did you
replace it with the name of a field in the report's record source?

Is the control in a group or report footer?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am afraid this didnt work.

Maybe someone can tell how to set the controlsource in a textbox to a
spesific column, and then tell me what formula to write, and where to write
it, for counting how many posts in the column that has a date(any date)
Click to expand...
Click to expand...
 
I have now found a way to do this :-)

I used: =Count([sluttkontroll utført])
and I used =Count([virkelig lev])

I learn a lot of do this things:-)
Thank you:-)


Twisty said:
I changed tfe expression to be:
=Count(IIF(Len([SLUTTKONTROLL UTFØRT] & "")>0,1,Null))
Click to expand...

SLUTTKONTROLL UTFØRT is the name of the column.

A error comes up and says: "The expression you entered has an invalid number"

You ask:
"Is the control in a group or report footer?"
If you mean where i have placed the textbox, which I want to count posts, I
have placed it in the report footer.

I
John Spencer said:
What does "I am afraid this didnt work." mean? Did you get an error, the
wrong results, no results, an explosion?

=Count(IIF(Len([Column2Field] & "")>0,1,Null))

That expression assumes that you have a field named Column2Field. Did you
replace it with the name of a field in the report's record source?

Is the control in a group or report footer?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am afraid this didnt work.

Maybe someone can tell how to set the controlsource in a textbox to a
spesific column, and then tell me what formula to write, and where to write
it, for counting how many posts in the column that has a date(any date)




:

Try the following

=Count(IIF(Len(Column2Field &"")>0,1,Null))
Click to expand...
Thanks in advance for help :-)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top