Selecting columns from a query by using a form

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

Yes, you can use form fields and combo boxes/listboxes to be part of the
expressions. You will need to use the correct column in parentheses if there
is more than one column in the recordsource of the combobox/listbox. You
need to have the fieldnames from the table included within double quotes, but
the values from the form controls need to be evaulated outside of any
double-quotes and appended to the statement. Text and date delimeters need
to be added within the double-quoted areas. You must choose the correct
column of the combo/list boxes for the data - this is determined by the
recordsource, not what you see in the listbox. (I made assumptions that both
combo boxes have only one field, which would be column 0.) If the
healthissues field was chosen from from combo2, you would need to write the
statement like this:

=Dcount "[Idclient]", "[" & Me.combo2.Column(0) & "]", "[age]= Between " &
Me.[age1from] & " and " &
Me.[age1to] & " AND [gender]= '" & [forms] & "'")

--
Daryl S


"Mafukufuku" wrote:

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

Thank you, this gives a bit of hope. Please correct the following:

1: I make a table with a column having the columnnames of all the columns to
be chosen
2: columnnames are typed in "" So an entry could be:
"[Dtblhealthissues]![coughing]"
3: I create a combo box with this table as row source, bound column to be
the one with the columnnames


4: "[" & Me.combo2.Column(0) & "]" is the syntax to get the value from the
table that is row source for combo2 into the expression.

Can i use similar syntax to define the first expression in the Dcount? I
mean: if I make a query with different columns but no IdClient, can i do
make a 2 columned combo-box, 1 collumn referring to the right query, 1
referring to the right column of that query?

= Dcount( "[" & Me.combo2.Column(0) & "]";"[" & Me.combo2.Column(1) &
"]";"[" & Me.combo2.Column(0) & "]=yes")

Column (0) having the column name "coughing", "shingles" etc but also
"Idbook", "Grant" and column (1) having the corresponding
query:"qryissuehealth" and "qryissuelegal"? Or do I get a problem with
using more columns from the combobox as Bound column?

This way people can just select the issue to be counted, either from legal
issues or from health issues. A pre-query might select the issues for the
right age and gender.

This would make my day.

I want users to be able to select from a listbox which column, ie which sum
they want to view.
It sounds like your data is stored in a spreadsheet format instead of a
relational database.
Is your table like this ---
Patient Coughing Shingles Disability
Smith x x
Brown x
Jones x x

It should be like this --
Patient Symptoms
Smith Coughing
Smith Disability
Brown Shingles
Jones Coughing
Jones Shingles

You can use a union query to align your data correctly --
SELECT Patient, "Coughing" AS Symptoms
FROM YourTable
WHERE [Coughing] = -1
UNION ALL SELECT Patient, "Shingles" AS Symptoms
FROM YourTable
WHERE [Shingles] = -1
UNION ALL SELECT Patient, "Disability" AS Symptoms
FROM YourTable
WHERE [Disability] = -1;

Then use a total query --
SELECT Symptoms, Count([Symptoms]) AS Patient_Count
FROM qryUnionPatientSymptoms
GROUP BY Symptoms;

--
Build a little, test a little.


"Mafukufuku" wrote:

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

See below...
--
Daryl S


"Mafukufuku" wrote:

Thank you, this gives a bit of hope. Please correct the following:

1: I make a table with a column having the columnnames of all the columns to
be chosen
2: columnnames are typed in "" So an entry could be:
"[Dtblhealthissues]![coughing]"
Do not include the table name, just the column name, without quotes or
brackets. You will add the quotes when you build the DLOOKUP statement. It
looks much better to the user if they only see the column names.
This table should look something like this:
coughing
sneezing
sweating

3: I create a combo box with this table as row source, bound column to be
the one with the columnnames
Yes!

4: "[" & Me.combo2.Column(0) & "]" is the syntax to get the value from the
table that is row source for combo2 into the expression.
Yes!

Can i use similar syntax to define the first expression in the Dcount? I
mean: if I make a query with different columns but no IdClient, can i do
make a 2 columned combo-box, 1 collumn referring to the right query, 1
referring to the right column of that query?
Yes you can, but I don't think you want to change the first expression of
Dcount, which is the field to count, usually the primary key field, since it
can't be null. DCount only counts non-null fields. You will be restricting
which records to count in the third expression, so you shouldn't change the
first epression.


= Dcount( "[" & Me.combo2.Column(0) & "]";"[" & Me.combo2.Column(1) &
"]";"[" & Me.combo2.Column(0) & "]=yes")

Column (0) having the column name "coughing", "shingles" etc but also
"Idbook", "Grant" and column (1) having the corresponding
query:"qryissuehealth" and "qryissuelegal"? Or do I get a problem with
using more columns from the combobox as Bound column?

This way people can just select the issue to be counted, either from legal
issues or from health issues. A pre-query might select the issues for the
right age and gender.

This would make my day.

Yes, you can do what you want, but this is how to approach it.

The DCOUNT function has three parameters.

The first is the field you want counted (usually the primary key field,
since the count returns non-null values in this field, and the primary key
field cannot be null).

The second is the table or query name where the data is stored. You can
pull this from a column in the combobox. Again, use the name of the query or
table in a column of the combobox, and reference it in the second parameter
of the query. Assuming column 1 has the query name:

= Dcount( "[KeyField]",""[" & Me.combo2.Column(1) & "]"", "record criteria
here - see next paragraph")

The third is the criteria for determining which records in the table/query
are counted. You want to restrict the records counted to those with
'coughing' or 'sneezing' in the right field, so that is where your combo-box
is used, to pick the right value. If what they are picking is a value that
could be in one field in the query (e.g. coughing could be a value in the
'Symptom' field), then the third expression would be (notice the single
quotes as text delimeters):

"[Symptom] = '" & Me.combo2.Column(2) & "'"

If instead, there is a column in the table called Coughing, and the value
could be true or false, then the criteria would be:

""[" & Me.combo2.Column(2) & "] = TRUE"

You can have multiple criteria in the third expression, each separated by
AND.

When you get the syntax down, if you have problems, post the syntax.

@ daryl S: thanks a lot. I will try and get it to work. Need to get to work
on learning VBA maybe, but this is a quick-fix.

Some background: the Dbase was created as a PDMS for a Homebased Care
project in Rural South Africa. We count clients, in families. They are
visited, their issues logged and a plan is made for appropriate Actions, also
logged. The project relies heavily on external funders. These funders
change their reporting format regularly or ask for new numbers in new
agegroups etc. Thats why we need the users to be able to extract data in a
dynamic way.

@ Karl Dewey: Thank you, I know. It angers my father a lot that I didnt do
it that way. He is a professional database designer, but not very good at
VBA, and he wasnt around in SA. So I messed up my Degrees of Normalisation.

"KARL DEWEY" wrote:

I want users to be able to select from a listbox which column, ie which sum
they want to view.
It sounds like your data is stored in a spreadsheet format instead of a
relational database.
Is your table like this ---
Patient Coughing Shingles Disability
Smith x x
Brown x
Jones x x

It should be like this --
Patient Symptoms
Smith Coughing
Smith Disability
Brown Shingles
Jones Coughing
Jones Shingles

You can use a union query to align your data correctly --
SELECT Patient, "Coughing" AS Symptoms
FROM YourTable
WHERE [Coughing] = -1
UNION ALL SELECT Patient, "Shingles" AS Symptoms
FROM YourTable
WHERE [Shingles] = -1
UNION ALL SELECT Patient, "Disability" AS Symptoms
FROM YourTable
WHERE [Disability] = -1;

Then use a total query --
SELECT Symptoms, Count([Symptoms]) AS Patient_Count
FROM qryUnionPatientSymptoms
GROUP BY Symptoms;

--
Build a little, test a little.


"Mafukufuku" wrote:

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

Daryl S: 'Computer says:"No."'


Here is my syntax: it's in a textbox, but keeps returning #Name?

=DCount("[" & Me.Combo6.Column(1) & "]";"[" & Me.Combo6.Column(3) & "]";"["
& Me.Combo6.Column(4) & "]=-1")

The combobox combo6 is made from this table dtblinserttable:

Autonumber IdClient Column Query
Countable value

1 IdClient Coughing Dtblissuehealth
-1
2 IdClient Shingles Dtblissuehealth
-1
3 IdClient Diarrhoea Dtblissuehealth
-1


The combobox is 4 column, bound column 1.
an extra textbox displays the bound columntext neatly, but the textbox with
the Dcountexpression doesnt even seem to change. Do I have the syntax
wrong, my columns mixed up or do I need to update the textbox?

Thanks!

Wow! it works!

For some reason:
=Dcount "[Idclient]", "[" & Me.combo2.Column(0) & "]", "[age]= Between " &
Me.[age1from] & " and " &
Me.[age1to] & " AND [gender]= '" & [forms] & "'")


Was not good. But removing the Me.'s from the expression and fiddling with
the columns a bit made it all work nicely.

What is the Me. about?

I managed to squeeze in another column in the table: countable Value. I can
now set the criteria to match the value that is set in this column. Some data
is y/n other is Positive/negative.

Thanks a lot for your assistance.

"Daryl S" wrote:


Yes, you can use form fields and combo boxes/listboxes to be part of the
expressions. You will need to use the correct column in parentheses if there
is more than one column in the recordsource of the combobox/listbox. You
need to have the fieldnames from the table included within double quotes, but
the values from the form controls need to be evaulated outside of any
double-quotes and appended to the statement. Text and date delimeters need
to be added within the double-quoted areas. You must choose the correct
column of the combo/list boxes for the data - this is determined by the
recordsource, not what you see in the listbox. (I made assumptions that both
combo boxes have only one field, which would be column 0.) If the
healthissues field was chosen from from combo2, you would need to write the
statement like this:

=Dcount "[Idclient]", "[" & Me.combo2.Column(0) & "]", "[age]= Between " &
Me.[age1from] & " and " &
Me.[age1to] & " AND [gender]= '" & [forms] & "'")

--
Daryl S


"Mafukufuku" wrote:

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

I'm glad it is working...

The Me. refers to a control on the currently active form or report. So if
you have code behind a form or a control on the form, you can refer to any
control (like a textbox or a listbox) as Me.[NameOfControl] without having to
specify the forms!formname to get there.

--
Daryl S


"Mafukufuku" wrote:

Wow! it works!

For some reason:
=Dcount "[Idclient]", "[" & Me.combo2.Column(0) & "]", "[age]= Between " &
Me.[age1from] & " and " &
Me.[age1to] & " AND [gender]= '" & [forms] & "'")


Was not good. But removing the Me.'s from the expression and fiddling with
the columns a bit made it all work nicely.

What is the Me. about?

I managed to squeeze in another column in the table: countable Value. I can
now set the criteria to match the value that is set in this column. Some data
is y/n other is Positive/negative.

Thanks a lot for your assistance.

"Daryl S" wrote:


Yes, you can use form fields and combo boxes/listboxes to be part of the
expressions. You will need to use the correct column in parentheses if there
is more than one column in the recordsource of the combobox/listbox. You
need to have the fieldnames from the table included within double quotes, but
the values from the form controls need to be evaulated outside of any
double-quotes and appended to the statement. Text and date delimeters need
to be added within the double-quoted areas. You must choose the correct
column of the combo/list boxes for the data - this is determined by the
recordsource, not what you see in the listbox. (I made assumptions that both
combo boxes have only one field, which would be column 0.) If the
healthissues field was chosen from from combo2, you would need to write the
statement like this:

=Dcount "[Idclient]", "[" & Me.combo2.Column(0) & "]", "[age]= Between " &
Me.[age1from] & " and " &
Me.[age1to] & " AND [gender]= '" & [forms] & "'")

--
Daryl S


"Mafukufuku" wrote:

I have querys with sums of patient-properties (they are symptoms like :
coughing, Shingles, disability etc etc.) They query is a summing query
making sums of all these properties for clients that can now be displayed,
ordered by age and gender.

I want users to be able to select from a listbox which column, ie which sum
they want to view.

How do I get a textbox to be bound to a column which is still to be selected
from a listbox. I tried making a table for the listbox: columns: Userchoice,
Fieldname where Fieldname is a text of the name of the column to be
selected.

Even better would be to be able to set the expression for a field to:

=Dcount "[Idclient]"; [healthissues]"; "[age]= Between
[forms]![form1monthlyreport][age1from] and
[forms]![form1monthlyreport]![age1to] AND [gender]=[combo1] AND
[combo2]![qry1sumofhealthissues]=-1")

So users enter gender, between Ages and select from combo2 a symptom to be
counted.

So the question is: can the value from a listbox/combobox be used as a
fieldname in an expression or to refer to a column in a query?

Thank you

On Thu, 25 Feb 2010 01:34:09 -0800, Mafukufuku
wrote:

Was not good. But removing the Me.'s from the expression and fiddling with
the columns a bit made it all work nicely.

What is the Me. about?

Me. or Me! in VBA code modules means "the name of the object containing this
code". It works in VBA but NOT in the control source of an object on the form
- there all you need is the name of the other object.
--

John W. Vinson [MVP]