Deciding WHICH field to display based on form control.

[Xx_TownDawg_xX]

Let's suppose for a minute that I have several different fields in a table, and they are ABC1, DEF1, GHI1, JKL1, etc..

Now let's suppose that based on a form entry, I would like for a query to display the particular field I'm looking for.. For example if I entered ABC in the form, I'd want ABC1 to display in the query, and if I entered DEF in the form, I'd want DEF1 to display in the query.

My mind is a little frazzled at the moment, but is there a fairly simple way to do this with a calculated field or VBA, or anything?

 

[ajetrumpet]

you cannot do this with sql, but vba will let you. for example...i have a form called FORM1, where TXT contains the fieldname I want to display. To make this work, the function that needs to be called is:

on error resume next

Dim db As Database
Set db = CurrentDb

db.QueryDefs.Delete "query name"

RefreshDatabaseWindow [COLOR="DarkGreen"]'ALTERNATIVE CODING[/COLOR]

db.CreateQueryDef "query name", "SELECT [" & Forms!form1!txt & "] FROM table"

RefreshDatabaseWindow [COLOR="DarkGreen"]'ALTERNATIVE CODING[/COLOR]

DoCmd.OpenQuery "query name"

 

[Xx_TownDawg_xX]

I'm looking.. not sure I have enough experience to figure out how to use this yet.