Selecting columns from a query by using a form

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.
"Daryl S" wrote:

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

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


"Mafukufuku" wrote:


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.
"Daryl S" wrote:

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

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.

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

--
Daryl S


"Mafukufuku" wrote:


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.

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 -

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


"Mafukufuku" wrote:

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

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.

"Daryl S" wrote:

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


"Mafukufuku" wrote:

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 -

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


"Mafukufuku" wrote:

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.

"Daryl S" wrote:

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


"Mafukufuku" wrote:

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

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

On Wed, 3 Mar 2010 01:45:01 -0800, Mafukufuku
wrote:

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

John W. Vinson [MVP]