How to chosse a column based on a criteria?

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hi. I need to create a query that chooses me a column based on a criteria.

Imagine. I have a form and in that form I a have a field with text.

So, in my query I choose a table with as many columns/fiels one of the
columns and many of them as the name that will appear on the text field on
form.

What I want is that my query loads only the column with the name that is on
that field on my form.

Imagime: if on my form the field as the YTD text I want that one of my
columns one query loads that column name.

Form Field: YTD

Query:

Name Address YTD



Form Field: YTG

Query:

Name Address YTG


Is is possible?


please help...

Marco
 
You cannot use a parameter to choose a field name in a query.

Consequently, you will need to build the query statement programmatically

This example assumes that you want to change Query1 so that it selects only
the field name you typed into a text box named Text0:
Dim strSql As String
strSql = "SELECT " & Me.Text0 & " FROM Table1;"
CurrentDb.QueryDefs("Query1").SQL = strSql
 
hi.

So, where do I put that code?

in form? or in another query? or it query 1 it selfs?

My problemi that I need to have a report over this query.


Regards,
Marco
 
Put it wherever suits you, Marco.

Perhaps you could put it in the Open event procedure of the report.
You don't even need a saved query: you can assign the query statement to the
RecordSource of the report.

However, if you have a text box bound to a field, and you have changing the
name of the field, then you will just get #Error on your report if the field
is missing.
 
Allen, I figured out of course it must be on form.

But is returning an error:

Run-time error '3075'
Syntax error in query expression
'148_qry_YTD:YTG_Reais_from_tbl15.YTDJuly'

I have the name first because there's more then one field with that name, so
I have to use the table name.

because the source is not a table but a query with 3 tables/queries.

Do I have to change the columns name in previous query?


by the way. Great solution here man.

Many thanks,
Marco
 
I'll put the code before printing.

This should append values to another table and then I put the report reading
the new table values.

What do you think?

Cheers,
Marco
 
Marco said:
... the source is not a table but a query with 3 tables/queries.

Do I have to change the columns name in previous query?
Click to expand...

Yes: if you change the column name in one query, you will need to also
change it in any other queries that use that query.
 
Can you just tell me how can I make her: strSql = "SELECT " & Me.Text88 & "
FROM 130_qry_YTD_YTG_Final;" as field.

Such as me.text88 as Teste


Thanks once again.
Marco
 
Why you use the wildcard:
SELECT * FROM 130_qry_YTD_YTG_Final;

Otherwise, go back to the previous example, and use the same approach for
this query too.
 
Back
Top