471,093 Members | 1,998 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,093 software developers and data experts.

Help With Expression Please

=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
3 1439
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
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
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.

Similar topics

7 posts views Thread by Ben Thomas | last post: by
7 posts views Thread by tjshadyluver | last post: by
1 post views Thread by TAM | last post: by
2 posts views Thread by hillcountry74 | last post: by
7 posts views Thread by Jeff | last post: by
1 post views Thread by Rahul | last post: by
14 posts views Thread by JNariss | last post: by
4 posts views Thread by MooMaster | last post: by
3 posts views Thread by Mr.Steskal | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.