 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / December 2005Help 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 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 > =DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2] > = '1'")+DCount("*","Clients","[Therapy3] = [quoted text clipped - 22 lines] > > TIA > 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 [quoted text clipped - 11 lines] > 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............... > 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 [quoted text clipped - 11 lines] > 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')" ) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.