Is it possible to use an expression to select field in query

[TinkerMan]

Hi

I though this was going to be really simple, but it was not. Maybe I'm overlooking something really simple (which will explain the lack of posts I found on it).

Consider a table with col1, col2, col3 etc. In a query you want to decide which column to use runtime i.e:
select colx, coly, whichColToUse(xyz) as specialColumn from z;

The idea is that whichColToUse might return "col2". If I do this, I get a column with the values "col2". Is there a way to use an expression to determine which column name to select, i.e make the selected column dynamic?

I know this is simple in VB, as the query is a string, but I cannot use VB for this (I assume), because I need to construct the final query for the form in VB. I think it will be a bit messy to select in VB from another query defined in VB.

To complicate matters, the underlaying query need a parameter, so when I tried a DLOOKUP, it failed.

Any input appreciated.

 

[Treason]

I think you're going to have to use VB to build the string. If the problem is using VB create one query then somehow use VB to create another query based off the first then use QueryDef.

Search the forums for QueryDef.. It basically allows you to modify a saved query, so this way you could later use VB again off that query...

Although something is telling me you have bad table design.

 

[Treason]

Whoops, I didnt realize Pat replied to this also... must've be simultaneous.

Treason bows to Pat's knowledge

 

[TinkerMan]

Thanks

Thanks for the replies.

I see the difference on static and dynamic SQL. So, that means that I need to either create temporary or named QueryDefs in VBA, and then use that as a foundation for my form query (which is also created in VBA).

I guess I need to use temporary QD's, as having named will affect other users as well. What is then the way of using the other QD in new QD. Do you get the name of the first QD and use that in the "from clause"?

Thanks again

 

[TinkerMan]

QueryDefs

I'm a little confused. When ever I need to query something in VBA I use the query string when creating a QueryDef. This is the only way that I know of that allows me to set query parameters:

  Dim rst As DAO.Recordset
  Dim qd As DAO.QueryDef
  Dim dbs As DAO.Database
  
  sql = "SELECT bla, bla from foo;"
  Set dbs = CurrentDb
  Set qd = dbs.CreateQueryDef("", sql)
  qd.Parameters![queryDate] = myDate
  Set rst = qd.OpenRecordset

So when I talk about temporary QueryDefs, I guess I am talking about unnamed QueryDef's, that has a name like "". According to Access help, this is a temporary QueryDef. I guess this QD will have a system generated name. So I guess what you're saying is that a QD cannot be temporary, as it needs to be physically saved somewhere, but this will allow me to create queries available to only one user, as they will be re-generated before every usage. Using static names for these queries will,as I see it, interfere with versions perhaps already in use by another user that is looking at the same form.

If these temporary QD is not that temporary, I need to make sure they are deleted after usage.

 

[TinkerMan]

Underlaying Query

Yes, that is the way I normally do it, but I have found this to be neccessary when the parameter is not part of the Query that the select is going against, but a query further down the chain. If I omit the "qd.Parameters![queryDate] = myDate" I get prompted for the date, which is not what I want, as I already know the date (from the form).