Select field name

[Gil Lopes via AccessMonster.com]

Hi guys!
Very noobish on Access here, so please forgive my ignorance.

I' m trying to make a query where one of the fields is selected by a form.
Something like this:

Expr1: (select fieldname from [tablename] where fieldname=[forms]![formname]!
[formfield])

Well, this does not work.
Should I state "select records from"?

Any help would be greatly appreciated.

Regards,

Gil

 

[Dennis]

I assume you mean that your query is based on criteria entered in a textbox
on a form.
If so then put the field from the table in a column in your query without
all the expr1 etc.
and in the criteria row of this column put

[Forms]![FormName]![FieldName]

 

[Gil Lopes via AccessMonster.com]

Thanks for the help Dennis!

The problem is that the query column is defined by the selection made on the
form.
I want the query to select the column where to gather data by aplying the
form data.

That's why I wanted something like "select field from table where fieldname
is = to form" in the field of the query.
Did I explained it clearly?

Again, many thanks!

Gil

I assume you mean that your query is based on criteria entered in a textbox
on a form.
If so then put the field from the table in a column in your query without
all the expr1 etc.
and in the criteria row of this column put

[Forms]![FormName]![FieldName]

Hi guys!
Very noobish on Access here, so please forgive my ignorance.

[quoted text clipped - 13 lines]

Gil

 

[John Vinson]

Hi guys!
Very noobish on Access here, so please forgive my ignorance.

I' m trying to make a query where one of the fields is selected by a form.
Something like this:

Expr1: (select fieldname from [tablename] where fieldname=[forms]![formname]!
[formfield])

Well, this does not work.
Should I state "select records from"?

Any help would be greatly appreciated.

Regards,

Gil

This sounds like you're storing DATA in fieldnames - a common design
mistake!

You cannot do what you ask in a Query. The only way would be to use
VBA code to construct the SQL view of a query. However, with proper
table design, *this should never be necessary*.

What is the structure of your table? What are the names of the fields
which the user can select?

John W. Vinson[MVP]

 

[Gil Lopes via AccessMonster.com]

Many thanks John!

We have a Fund management company.
All I want is to store prices from our funds, by date.
So, my table has [date],[fund1],[fund2],[fund3] as fields.

The idea is that records only have the proper date and prices for our 5 funds.

I want the user to select the proper field in a form's combobox (the chosen
fund) and to transport that choice to the query field.

Haven't been able to, yet.

Regards,

Gil

Hi guys!
Very noobish on Access here, so please forgive my ignorance.

[quoted text clipped - 13 lines]

Gil

This sounds like you're storing DATA in fieldnames - a common design
mistake!

You cannot do what you ask in a Query. The only way would be to use
VBA code to construct the SQL view of a query. However, with proper
table design, *this should never be necessary*.

What is the structure of your table? What are the names of the fields
which the user can select?

John W. Vinson[MVP]

 

[John Vinson]

Many thanks John!

We have a Fund management company.
All I want is to store prices from our funds, by date.
So, my table has [date],[fund1],[fund2],[fund3] as fields.

That is an INCORRECT DESIGN and that's why you're having trouble.

The idea is that records only have the proper date and prices for our 5 funds.

So if, at *any* time in the future, you add a sixth fund, or delete a
fund, or you change a fund's name - what? Redesign your table, all
your queries, all your forms, all your reports? Certainly not!

If you have a Many (clients) to Many (funds) relationship, a better
design would have THREE tables:

Clients
ClientID
<client contact information>

Funds
FundName
Description

Investments
ClientID <link to Clients>
FundName <link to Funds>
InvestmentDate
Price
<maybe other info about this fund on this date>

You would then have only ONE field to search.

I want the user to select the proper field in a form's combobox (the chosen
fund) and to transport that choice to the query field.

Easy with a properly normalized design; very difficult indeed with
your current "spreadsheet" approach.

John W. Vinson[MVP]

 

[Duane Hookom]

Reply in newer thread that should have been here.

Gil, you need to be more patient with this and take the advice from the
master (John Vinson).

--
Duane Hookom
MS Access MVP
--

Many thanks John!

We have a Fund management company.
All I want is to store prices from our funds, by date.
So, my table has [date],[fund1],[fund2],[fund3] as fields.

The idea is that records only have the proper date and prices for our 5
funds.

I want the user to select the proper field in a form's combobox (the
chosen
fund) and to transport that choice to the query field.

Haven't been able to, yet.

Regards,

Gil

Hi guys!
Very noobish on Access here, so please forgive my ignorance.

[quoted text clipped - 13 lines]

Gil

This sounds like you're storing DATA in fieldnames - a common design
mistake!

You cannot do what you ask in a Query. The only way would be to use
VBA code to construct the SQL view of a query. However, with proper
table design, *this should never be necessary*.

What is the structure of your table? What are the names of the fields
which the user can select?

John W. Vinson[MVP]

 

[Gil Lopes via AccessMonster.com]

Many thanks, John!

I' m redesigning right now : )
I said I was noobish...

Sincere regards,


Gil

Many thanks John!

We have a Fund management company.
All I want is to store prices from our funds, by date.
So, my table has [date],[fund1],[fund2],[fund3] as fields.

That is an INCORRECT DESIGN and that's why you're having trouble.

The idea is that records only have the proper date and prices for our 5 funds.

So if, at *any* time in the future, you add a sixth fund, or delete a
fund, or you change a fund's name - what? Redesign your table, all
your queries, all your forms, all your reports? Certainly not!

If you have a Many (clients) to Many (funds) relationship, a better
design would have THREE tables:

Clients
ClientID
<client contact information>

Funds
FundName
Description

Investments
ClientID <link to Clients>
FundName <link to Funds>
InvestmentDate
Price
<maybe other info about this fund on this date>

You would then have only ONE field to search.

I want the user to select the proper field in a form's combobox (the chosen
fund) and to transport that choice to the query field.

Easy with a properly normalized design; very difficult indeed with
your current "spreadsheet" approach.

John W. Vinson[MVP]

 

[Gil Lopes via AccessMonster.com]

You're quite right, Douane!

I' m taking John' s advice.
Yes, I need to be more patient. It's a time zone problem, I guess.

Anyway, I got your reply from the other thread (sorry about that) and we'll
keep here for a future need.

Again, many thanks!

Gil

Reply in newer thread that should have been here.

Gil, you need to be more patient with this and take the advice from the
master (John Vinson).

Many thanks John!

[quoted text clipped - 32 lines]