Concantenated Field Name?

[rb608]

I have a table wherein all of the data is separated into fields according to
its respective quarter of the year. That is, all data for the first
quarter of 2003 is in field name [2003:1]. That is a given; I didn't set
it up that way, and I can't change it.

What I want is to use an existing form where the user enters the year and
quarter number, and use the contents of those two text boxes to create the
appropriate field name in the Select statement of a query. (e.g.
concantenate 2003 and 1 to form the field name [2003:1].)

It seems to me this might be possible, but the syntax is beyond anything
I've done to date.

TIA,
Joe

 

[Dirk Goldgar]

I have a table wherein all of the data is separated into fields
according to its respective quarter of the year. That is, all data
for the first quarter of 2003 is in field name [2003:1]. That is a
given; I didn't set it up that way, and I can't change it.

Bad design, but you knew that.

What I want is to use an existing form where the user enters the year
and quarter number, and use the contents of those two text boxes to
create the appropriate field name in the Select statement of a query.
(e.g. concantenate 2003 and 1 to form the field name [2003:1].)

It seems to me this might be possible, but the syntax is beyond
anything I've done to date.

You can build the SQL for the query easily enough, in code behind the
form. Here's a simple example:

Dim strFieldName As String
Dim strSQL As String

strFieldName = "[" & Me!txtYear & ":" & Me!txtQuarter & "]"

strSQL = "SELECT " & strFieldName & " FROM MyTable;"

Once you've got the SQL, what do you want to do with it? If you want to
open a recordset on it or set a recordsource or rowsource to it, you
have all you need. If you want to set the SQL of a stored query to this
SQL string, you need to manipulate the corresponding DAO QueryDef
object.

 

[rb608]

Bad design, but you knew that.

Yeah. It's data exported from an external source; & that's how it's
arranged.

You can build the SQL for the query easily enough, in code behind the
form. Here's a simple example:

Dim strFieldName As String
Dim strSQL As String

strFieldName =

strSQL = "SELECT " & strFieldName & " FROM MyTable;"

Good for me; that's more or less how I set it up the first time I tried,
but...

If you want to set the SQL of a stored query to this
SQL string, you need to manipulate the corresponding DAO QueryDef
object.

Yes, using it in a stored query is what I would like to do. Using this
code in the SQL of the query:

SELECT "[" & Forms!QtrReportForm!txtYear & ":" &
Forms!QtrReportForm!txtQuarter & "]" FROM DataTable

....I'm getting "[2003:1]" as the contents of the field Expr1 instead of the
field name. What do I need to do to make it see this as the field name from
which I want to pull the data?

TIA again,
Joe

 

[Dirk Goldgar]

Bad design, but you knew that.

Yeah. It's data exported from an external source; & that's how it's
arranged.

You can build the SQL for the query easily enough, in code behind the
form. Here's a simple example:

Dim strFieldName As String
Dim strSQL As String

strFieldName =

strSQL = "SELECT " & strFieldName & " FROM MyTable;"

Good for me; that's more or less how I set it up the first time I
tried, but...

If you want to set the SQL of a stored query to this
SQL string, you need to manipulate the corresponding DAO QueryDef
object.

Yes, using it in a stored query is what I would like to do. Using
this code in the SQL of the query:

SELECT "[" & Forms!QtrReportForm!txtYear & ":" &
Forms!QtrReportForm!txtQuarter & "]" FROM DataTable

...I'm getting "[2003:1]" as the contents of the field Expr1 instead
of the field name. What do I need to do to make it see this as the
field name from which I want to pull the data?

Are you trying to change the SQL of an existing query, or create a new
one? I'll assume it's an existing query. Try something like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("MyStoredQuery")

qdf.SQL = _
"SELECT [" & Forms!QtrReportForm!txtYear & ":" & _
Forms!QtrReportForm!txtQuarter & "] FROM DataTable;"

Set qdf = Nothing
Set db = Nothing