Real problems with DCount

[deaconj999]

I need to count the number of values in a column that have been
recorded with the Yes/No tick box on the main form of my database

So far, I have tried

=DCount("*","[Table1]","[sleep risk]='true'")

=DCount("*","[Table1]","[sleep risk]='yes'")

and a few others that don't work either.

Secondly, I also have to count a number of dates entered in a column
[date fsmp sent] to give me the total number of date entries in that
column that were witihn the last 90 days from Date().

I don't know if Date() or Now() is best for this and have used the
below formula but again it's not right.

=DCount("*","[Table1].[date fsmp sent]='date()-90'")

What am I doing wrong here with both of these formula

A quick repsonse would be grestly appreciated.

 

[Douglas J. Steele]

I need to count the number of values in a column that have been
recorded with the Yes/No tick box on the main form of my database

So far, I have tried

=DCount("*","[Table1]","[sleep risk]='true'")

=DCount("*","[Table1]","[sleep risk]='yes'")

and a few others that don't work either.

=DCount("*","[Table1]","[sleep risk]=True")

(no quotes)

You could also use

=DCount("*","[Table1]","[sleep risk]=-1")

Secondly, I also have to count a number of dates entered in a column
[date fsmp sent] to give me the total number of date entries in that
column that were witihn the last 90 days from Date().

I don't know if Date() or Now() is best for this and have used the
below formula but again it's not right.

Now includes both date and time, so it's extremely doubtful that's what you
want.

=DCount("*","[Table1].[date fsmp sent]='date()-90'")

Again, remove the quotes:

=DCount("*","[Table1].[date fsmp sent]=date()-90")

or

=DCount("*","[Table1].[date fsmp sent]=DateAdd("d", -90, date())")

 

[deaconj999]

I need to count the number of values in a column that have been
recorded with the Yes/No tick box on the main form of my database

So far, I have tried

=DCount("*","[Table1]","[sleep risk]='true'")

=DCount("*","[Table1]","[sleep risk]='yes'")

and a few others that don't work either.

=DCount("*","[Table1]","[sleep risk]=True")

(no quotes)

You could also use

=DCount("*","[Table1]","[sleep risk]=-1")

Secondly, I also have to count a number of dates entered in a column
[date fsmp sent] to give me the total number of date entries in that
column that were witihn the last 90 days from Date().

I don't know if Date() or Now() is best for this and have used the
below formula but again it's not right.

Now includes both date and time, so it's extremely doubtful that's what you
want.

=DCount("*","[Table1].[date fsmp sent]='date()-90'")

Again, remove the quotes:

=DCount("*","[Table1].[date fsmp sent]=date()-90")

or

=DCount("*","[Table1].[date fsmp sent]=DateAdd("d", -90, date())")

Hi Doug,

The tip about the quote works but the date bit doesn't, I can get
this to work in the form even though it gives me a minus value count
of the dates entered in the [date fsmp sent] column but the report
doesn't like it.

Any ideas please ?

=Sum([Date FSMP sent]>=DateAdd("m",-3,Date()))

 

[Tom Lake]

Secondly, I also have to count a number of dates entered in a column

[date fsmp sent] to give me the total number of date entries in that
column that were witihn the last 90 days from Date().

I don't know if Date() or Now() is best for this and have used the
below formula but again it's not right.

Now includes both date and time, so it's extremely doubtful that's what
you want.

=DCount("*","[Table1].[date fsmp sent]='date()-90'")

Again, remove the quotes:

=DCount("*","[Table1].[date fsmp sent]=date()-90")

He wants all dates WITHIN the previous 90 days not just those that are
exactly 90 days back. Shouldn't this do it?

=DCount("*","[Table1].[date fsmp sent] >= date()-90")

Tom Lake

 

[Duane Hookom]

It looks like your domain aggregate function is missing the "domain"
=DCount("*","[Table1].[date fsmp sent]=date()-90")
try;
=DCount("*","[Table1]", "[date fsmp sent]>=date()-90")


--
Duane Hookom
Microsoft Access MVP

I need to count the number of values in a column that have been
recorded with the Yes/No tick box on the main form of my database

So far, I have tried

=DCount("*","[Table1]","[sleep risk]='true'")

=DCount("*","[Table1]","[sleep risk]='yes'")

and a few others that don't work either.

=DCount("*","[Table1]","[sleep risk]=True")

(no quotes)

You could also use

=DCount("*","[Table1]","[sleep risk]=-1")

Secondly, I also have to count a number of dates entered in a column
[date fsmp sent] to give me the total number of date entries in that
column that were witihn the last 90 days from Date().

I don't know if Date() or Now() is best for this and have used the
below formula but again it's not right.

Now includes both date and time, so it's extremely doubtful that's what you
want.

=DCount("*","[Table1].[date fsmp sent]='date()-90'")

Again, remove the quotes:

=DCount("*","[Table1].[date fsmp sent]=date()-90")

or

=DCount("*","[Table1].[date fsmp sent]=DateAdd("d", -90, date())")

Hi Doug,

The tip about the quote works but the date bit doesn't, I can get
this to work in the form even though it gives me a minus value count
of the dates entered in the [date fsmp sent] column but the report
doesn't like it.

Any ideas please ?

=Sum([Date FSMP sent]>=DateAdd("m",-3,Date()))