Selecting columns from a query by using a form

[Mafukufuku]

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]

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] & "'")

 

[Mafukufuku]

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.

 

[KARL DEWEY]

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;

 

[Daryl S]

See below...
--
Daryl S

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.

 

[Mafukufuku]

@ 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.

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.

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

 

[Mafukufuku]

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!

 

[Mafukufuku]

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.

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

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]

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

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.

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

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

 

[John W. Vinson]

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.

 

[Mafukufuku]

All right,


The next try was this: to make a query in design view that has a modifiable
fieldname and table name. But using the same expression: [" &
Combo29.Column(2) & "] as field name and [" & Combo29.Column(2) & "] as tabel
name does not work.

Can this be done aswell?

Thanks.

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

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.

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


:

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]

Yes, it can. You just need to provide the full form name before the control
name. Like this:

Forms!<yourFormName>.Combo29.Column(2)

You can use the Expression Builder in query design to help navigate there so
there are fewer typos.

--
Daryl S

All right,


The next try was this: to make a query in design view that has a modifiable
fieldname and table name. But using the same expression: [" &
Combo29.Column(2) & "] as field name and [" & Combo29.Column(2) & "] as tabel
name does not work.

Can this be done aswell?

Thanks.

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

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.

:


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


:

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

 

[Mafukufuku]

Sorry, no luck.

Expr3: Forms!frm1inserttable.Combo29.Column(2)

is the exact text of the field. I get the (translated from Dutch) error:

The expression contains an undefined error
Expr3:Forms!frm1inserttable.Combo29.Column(2)

extra questions: to make the query even run in Datasheet I need to insert
some tables dont I? The one I am using for the combobox or the ones the
query is going to use once defined? I guess the last one.

Do I specify the Table name underneath the field expr3?

Hope you can help again.

 

[Daryl S]

Other than the Column(2) piece, did you build the rest using Expression
Builder? That could resolve some issues. Please post the whole SQL (use SQL
View in query design and copy/paste into your next posting).

 

[Mafukufuku]

SELECT Forms![frm1inserttable]![Combo29].Column(2) AS Expr1,
Forms![frm1inserttable]![Combo29].Column(1) AS Expr2;


Do I select the tables involved (via de combobox) from the field list?

I get a Undefined function error for Forms![frm1inserttable]![combo29].column.

So he doesnt list the (2) from behind Column in this error?

What I would want is to select fieldnames into this query that are
references to a column in another query.

Query 1 selects [IdClient] of all patients visited in a certain period in
the Database from Dtblclients
Query 2 selects [Idclient] of only OVC's (Orphans and Vulnerable Children,
clients who meet specific criteria) visited in a certain period from
DtblClient

Query 3 finds for either group the actions and issues. Whether this is
group 1 or group2 is to be decided by the user. So a listbox/combobox to
select query1 or query2 to be part of query 3.

I gather if I get this to work with a table, as in the above trial
(Dtblissuehealth) I can get it to work work with querys as well.

Thanks

 

[Daryl S]

Mafukufuku -

I think I may have mis-stated a piece earlier. You can build this query
using the information from the combobox on the forms, but I think you want to
create the SQL in the form. You can't evaulate an expression within query
builder to come up with the table or field names. You can build the SQL
statement in code on a button on the form (which I think is where we
started). You can also save this SQL as a query that you can look at in
query design mode, but anything in query design mode will have to have the
table and fields defined.

Does that make sense?

 

[Mafukufuku]

Thank you it does,

to a certain extent.

I have two accept 2 things:

1: that have not yet mastered access
2: that it seems that the interchangeability between SQL, Designmode and VBA
seems limited, whereas I thought Designmode was the poor mans SQL, which was
the poor mans VBA.

Give me some time to sort your last tip out, if "Computer says:"No!"" I will
get back to you. Thanks again.

Mafukufuku -

I think I may have mis-stated a piece earlier. You can build this query
using the information from the combobox on the forms, but I think you want to
create the SQL in the form. You can't evaulate an expression within query
builder to come up with the table or field names. You can build the SQL
statement in code on a button on the form (which I think is where we
started). You can also save this SQL as a query that you can look at in
query design mode, but anything in query design mode will have to have the
table and fields defined.

Does that make sense?

--
Daryl S

SELECT Forms![frm1inserttable]![Combo29].Column(2) AS Expr1,
Forms![frm1inserttable]![Combo29].Column(1) AS Expr2;


Do I select the tables involved (via de combobox) from the field list?

I get a Undefined function error for Forms![frm1inserttable]![combo29].column.

So he doesnt list the (2) from behind Column in this error?

What I would want is to select fieldnames into this query that are
references to a column in another query.

Query 1 selects [IdClient] of all patients visited in a certain period in
the Database from Dtblclients
Query 2 selects [Idclient] of only OVC's (Orphans and Vulnerable Children,
clients who meet specific criteria) visited in a certain period from
DtblClient

Query 3 finds for either group the actions and issues. Whether this is
group 1 or group2 is to be decided by the user. So a listbox/combobox to
select query1 or query2 to be part of query 3.

I gather if I get this to work with a table, as in the above trial
(Dtblissuehealth) I can get it to work work with querys as well.

Thanks

 

[Daryl S]

Mafukufuku -

When you get a handle on things, and are ready for more help, open a new post.

As for VBA, SQL, and Query Design...

SQL is a language for talking to relational databases. For the most part,
we think of it as a tool for getting data into and out of a database, mostly
reporting on data in a database. Query Design is a great tool for making
many of the queries simple to produce. You can create the queries in Query
Design and then switch to SQL View to see the SQL text. Now there are some
types of queries that can only be done in SQL View, such as UNION queries and
Pass-thru queries, but most of the basic types of queries can be done in
Query Design, so use it as a leaning tool.

VBA on the other hand is an application development language. It cannot
talk to relational databases itself, but it can pass SQL statement to a
database and receive the results back. VBA also manages the user interface
(e.g. forms), and can perform calculations and make decisions based on
information provided by the end user or other events. It can be used to open
up some other applications (e.g. Excel or Word) and to 'talk' to the
operating system.

While VBA itself does not manipulate relational databases, it has the
capability to send SQL statement and to receive back results. The SQL
statements it sends can be from queries we have already saved in Access, or
we can build the SQL text within the code, as VBA allows us to manipulate
string variables. Of course we (the developers) have to know how to build
these statements as we have to use the VBA language to assemble the parts.
We can use VBA to pull information off of our open forms or from other
sources and then put them into a SQL statement. It's pretty awesome, really.

Anyway, good luck on your project...
--
Daryl S

Thank you it does,

to a certain extent.

I have two accept 2 things:

1: that have not yet mastered access
2: that it seems that the interchangeability between SQL, Designmode and VBA
seems limited, whereas I thought Designmode was the poor mans SQL, which was
the poor mans VBA.

Give me some time to sort your last tip out, if "Computer says:"No!"" I will
get back to you. Thanks again.

Mafukufuku -

I think I may have mis-stated a piece earlier. You can build this query
using the information from the combobox on the forms, but I think you want to
create the SQL in the form. You can't evaulate an expression within query
builder to come up with the table or field names. You can build the SQL
statement in code on a button on the form (which I think is where we
started). You can also save this SQL as a query that you can look at in
query design mode, but anything in query design mode will have to have the
table and fields defined.

Does that make sense?

--
Daryl S

SELECT Forms![frm1inserttable]![Combo29].Column(2) AS Expr1,
Forms![frm1inserttable]![Combo29].Column(1) AS Expr2;


Do I select the tables involved (via de combobox) from the field list?

I get a Undefined function error for Forms![frm1inserttable]![combo29].column.

So he doesnt list the (2) from behind Column in this error?

What I would want is to select fieldnames into this query that are
references to a column in another query.

Query 1 selects [IdClient] of all patients visited in a certain period in
the Database from Dtblclients
Query 2 selects [Idclient] of only OVC's (Orphans and Vulnerable Children,
clients who meet specific criteria) visited in a certain period from
DtblClient

Query 3 finds for either group the actions and issues. Whether this is
group 1 or group2 is to be decided by the user. So a listbox/combobox to
select query1 or query2 to be part of query 3.

I gather if I get this to work with a table, as in the above trial
(Dtblissuehealth) I can get it to work work with querys as well.

Thanks

 

[Mafukufuku]

Thank you all. The function below works excelent. Combo29 refers to a table
that lists the columnnames and qrynames. text 31 is controlled by the value I
want in the criterium (mostly -1 for yesses). I would now like to be able to
dcount IdClient in any column where there is a yes. In Column(2) the
entries like:
coughing
shingles
diarrhoea

Works fine. but what to enter as a wildcard? * or [*] or "*" doesn't work.

So what can one use as a wildcard in Dcount to use any column in the
tablereferred to as a criterium-column?

=DCount("[" & Combo29.Column(1) & "]";"[" & Combo29.Column(3) & "]";"[" &
Combo29.Column(2) & "]=[text31]")

 

[John W. Vinson]

Thank you all. The function below works excelent. Combo29 refers to a table
that lists the columnnames and qrynames. text 31 is controlled by the value I
want in the criterium (mostly -1 for yesses). I would now like to be able to
dcount IdClient in any column where there is a yes. In Column(2) the
entries like:
coughing
shingles
diarrhoea

Works fine. but what to enter as a wildcard? * or [*] or "*" doesn't work.

So what can one use as a wildcard in Dcount to use any column in the
tablereferred to as a criterium-column?

=DCount("[" & Combo29.Column(1) & "]";"[" & Combo29.Column(3) & "]";"[" &
Combo29.Column(2) & "]=[text31]")

You can't. You would need different code to construct a much more complex SQL.
And I don't understand the logic. You want to count records where ANY of the
fields is true?

You're really making your job much, much harder by insisting on keeping your
data in this non normalized format. At the very least you should construct a
normalized three-table system as discussed upthread and migrate the data from
your current table into it. You'll make your queries VASTLY easier.