By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,135 Members | 1,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,135 IT Pros & Developers. It's quick & easy.

Help With Expression Please

P: n/a
=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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.