Combobox to define counted column

[clemensbarends]

How can i get the value from a combobox to be used as the column that needs to be searched/counted in an already existing query?

I have a query with patient data: qryissuehealth. It has Idpatient (linking it to personal data) and several columns about complaint: "Shingles", "Coughing", "Diarrhoea" etc etc. I now want to build a form where a combobox drops down displaying these complaints. Next they are to be selected and a Dcount-function is to count these complaint:

I have tried this in almost every conceivable combination of []'s, ()'s and ""'s but it doesnt work.

Dcount ("[IDclient]![Dtblissuehealth]";"[Dtblissuehealth]"; "[combo4]=yes")

=DCount("[IDclient]![Dtblissuehealth]";"[dtblissuehealth]";""[combo4]"&!"[dtblissuehealth]""=Yes)


Note that the field [combobox4]![Dtblissuehealth] does not actually exist but needs to be filled in with the result from combobox4.

The bound column of combo4 refers to Dtblinserttable: a table where the bound column hold the wanted columnnames as text: [coughing]![Dtblissuehealth] etc. Or, in the above examples: [coughing]

I cant do VBA, have to work with ingenuity and stamina in the expression builder. I would like to keep it in expressionbuilder.

can you get the criterium-column in the dcount function to be filled with the result from a combobox?

Any workaround with an additional query is also welcome ofcourse.

Thank you!

 

[Galaxiom]

Where you need to refer to a field the object is written first. You have everything backwards.
tablename.fieldname or tablename!fieldname or Forms!formname!controlname etc

You need to reread the construction of Domain functions.

DCount("fieldname/expression","table/queryname","{condition}")
The condition must refer to a field in the domain.

I would not express the symptom as fields but as records. As a general rule most new developers need to head in the direction of less fields and more records.

Search normalization on this site to get a handle on the bigger picture of good database structuring.

Stop telling yourself you can't do VBA. It isn't as hard as it first appears. Moreover it can do things that macros can't even touch. A little ingenuity and stamina in VBA will take you much further than the expression builder.

 

[clemensbarends]

Thank you,

but it hasnt helped. I now have a query (qry1sumofhealthages) listing these columns:

IdClient / Age / Gender / Coughing:Coughing / Shingles:Shingles / Diarrhoeaiarrhoea

My forms uses Combo6 to get a textual record from Dtblinserttable:

Autonumber: Column:
1 Coughing
2 Shingles
3 Diarrhoea

I hoped by 'naming' the columns with the symptoms I could fool the expression

=DCount("[qry1sumofhealthage]![IdClient]";"[qry1sumofhealthage]";"[Combo6]=-1")

into using the result of Combo6 (ie 'Diarrhoea' or 'Shingles' from the dtbl) as the reference for the criterium. That is my real problem. How do I get the combobox to navigate between the columns in the query that need to be Dcounted. How to type combo6 so its content is read as a columnhead:
['[combo6]'] or ["[combo6]"]

I want to count numbers of clients suffering from Diarrhoea or those suffering from Shingles.

The point is that if this works I will again make a selection based on age groups that users can define on a form (already done).

Thanks again. will look into VBA one day. already happy with what I can do right now. My father is a professional Dbase designer, alas not VBA programmer and COBOL is a bit expensive. So yes, I know about the Degrees of Normalisation.

 

[georgedwilkinson]

Re-read what Galaxiom said and do that. Don't design your table that way. What happens if you get a new complaint? Are you gonna totally rewrite your entire system to accommodate the new value as a column?

If you insist on going down the road you're going down, you would be better off using a spreadsheet.

The point is, each new complaint should have its own row in a table designed specifically for a patient's complaints. There should be a reference (aka Foreign Key) to the key of the patient table and a reference to the key from the table where you will look up the stock complaints (like Shingles, etc.).

When you design it correctly, you'll find most of this stuff just works magically and you don't even need to deal with VBA or macros (which suck, BTW). Just a little work with the query editor will do you nicely, usually.