Help With Expression Please

Saxman

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'")

The above works fine. It sums up the number of therapists who specialise
in a particular treatment. I need to add which therapists are active
(available). I have tried doing this with the AND function, but I have not
got it quite right.

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'") AND [Active] = '1'"

It's the bit on the end that does not work. I have tried adding/removing
quotes to no avail. The 'Active' column comes from the same table. Active
can be 1 or 2 (not active).

I have also posted this to comp.databases.ms-access

TIA

Dec 1 '05 #1

Larry Linson

Your statement is attempting to AND [Active] = 1 to the sum of the DCounts.
I seriously doubt that was your intent. You cannot AND additional criteria
to each DCount with this statement which I think was your intent.

Consider including that Criteria in a Query that you use as the basis of the
DCounts instead of the table "Clients".

As an aside, it appears you have multiple fields for the same purpose
defined in your Table, which is not good relational design practice. You
might find life easier if you redesig to use a Therapy table, with each
record indicating the kind of therapy and related to a record in the Client
table.

Larry Linson
Microsoft Access MVP

"Saxman" <john.h.williams(removethis)@btinternet.com> wrote in message
news:12******************************@40tude.net.. .

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'")

The above works fine. It sums up the number of therapists who specialise
in a particular treatment. I need to add which therapists are active
(available). I have tried doing this with the AND function, but I have
not
got it quite right.

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'") AND [Active] = '1'"

It's the bit on the end that does not work. I have tried adding/removing
quotes to no avail. The 'Active' column comes from the same table.
Active
can be 1 or 2 (not active).

I have also posted this to comp.databases.ms-access

TIA

Dec 1 '05 #2

Saxman

On Thu, 01 Dec 2005 17:14:33 GMT, Larry Linson wrote:

Your statement is attempting to AND [Active] = 1 to the sum of the DCounts.
I seriously doubt that was your intent. You cannot AND additional criteria
to each DCount with this statement which I think was your intent.

Consider including that Criteria in a Query that you use as the basis of the
DCounts instead of the table "Clients".

As an aside, it appears you have multiple fields for the same purpose
defined in your Table, which is not good relational design practice. You
might find life easier if you redesig to use a Therapy table, with each
record indicating the kind of therapy and related to a record in the Client
table.

Larry Linson
Microsoft Access MVP

Thanks for that. I am in agreement with what you state regarding different
tables. Trouble is, these customers keep asking for more, and I ain't
getting paid for it...............

Dec 1 '05 #3

Saxman

On Thu, 01 Dec 2005 17:14:33 GMT, Larry Linson wrote:

Your statement is attempting to AND [Active] = 1 to the sum of the DCounts.
I seriously doubt that was your intent. You cannot AND additional criteria
to each DCount with this statement which I think was your intent.

Consider including that Criteria in a Query that you use as the basis of the
DCounts instead of the table "Clients".

As an aside, it appears you have multiple fields for the same purpose
defined in your Table, which is not good relational design practice. You
might find life easier if you redesig to use a Therapy table, with each
record indicating the kind of therapy and related to a record in the Client
table.

Larry Linson
Microsoft Access MVP

This worked eventually.

=DCount("*","Clients","Active = '1' AND (Therapy1='1' OR Therapy2='1' OR
Therapy3='1' OR Therapy4='1' OR Therapy5='1' OR Therapy6='1' OR Therapy6
='1')" )

Dec 1 '05 #4