query with changing field name

  • Thread starter Thread starter Eddy Brauns
  • Start date Start date
E

Eddy Brauns

Hi,

This is my SQL-statement for January 2003:

SELECT mk_ttfgld012500.t_amnt_1
FROM mk_ttfgld012500
WHERE (((mk_ttfgld012500.t_nlac) Like " 70*") AND
((mk_ttfgld012500.t_year)=2003));

The last 2 digits (XX) of the field t_amnt_XX in the SQL-string represent a
period number ranging from 1 to 12.

Is it possible to change the field name "t_amnt_XX" in this SQL string using
VBA-code?
(MS Access97)

Eddy Brauns
 
On my website is a small sample database called CreateQueries2.mdb which
shows how to use VBA to programmatically change queries.
 
Hi,

This is my SQL-statement for January 2003:

SELECT mk_ttfgld012500.t_amnt_1
FROM mk_ttfgld012500
WHERE (((mk_ttfgld012500.t_nlac) Like " 70*") AND
((mk_ttfgld012500.t_year)=2003));

The last 2 digits (XX) of the field t_amnt_XX in the SQL-string represent a
period number ranging from 1 to 12.

Is it possible to change the field name "t_amnt_XX" in this SQL string using
VBA-code?
(MS Access97)
Click to expand...

Sure... but I'd STRONGLY suggest normalizing your data instead. If you
have fields storing data (period numbers) in fieldnames, you're
storing a one-to-many relationship IN EACH RECORD. Storing data in
fieldnames is almost always a bad design, for exactly this reason - it
makes it much more complicated to query your data.

You'll need to write VBA code to build this SQL string up from
scratch: e.g.

Dim strSQL As String
strSQL = "SELECT mk_ttfgld012500.t_amnt_" & intPeriod _
& " FROM ... <etc>

and then assign this SQL to the recordsource property of a form or
report, or create a Recordset based on it.
 
Back
Top