Access 2007 ODBC Select column given a portion of the name of the column
Posted on 2011-09-07
I currently am doing a passthru query (QRY_GENERAL_GL_PASSTHRU_DATES) in Access 2007 to an ODBC table to return columns which indicate the current period reference (for example, 33) and all the related dates to all the current period for the start (Periods33FrmDate) and the end (Periods33ToDate). The odd thing (this link is to Peachtree GL) is the array is all on one row with each of the column dates being a separate column. For example, all the necessary data is on one row, with Peachtree already supplying 33 in the CurrentPeriod column. The entire row is comprised of an array with column names of Periods0FrmDate to Periods40FrmDate (41 columns) and Periods0ToDate to Periods40ToDate. I just want to return the columns with the "33" in the middle of the name. The concatenation piece continues to blow up, but if hard coded as Periods33FrmDate works fine.
SELECT AcctgModule,CurrentPeriod, "Periods"&CurrentPeriod&"FrmDate", "Periods"&CurrentPeriod&"ToDate" where AcctgModule=4