How to chosse a column based on a criteria?

[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

 

[Allen Browne]

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

 

[Marco]

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

 

[Allen Browne]

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.

 

[Marco]

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

 

[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

 

[Allen Browne]

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

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

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.

 

[Marco]

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

 

[Allen Browne]

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.