Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How refer to the Query Column Names by code

Posted on 2003-03-26
Medium Priority
Last Modified: 2006-11-17
Please help.

I have a crosstab query where the column names are changing every Year.

The report is for getting totals for past 3 years + the year I input.
eg. If I input 2001 the report will be for 1998, 1999, 2000 and 2001

I have created a crosstab query which I am referring to in codes below

Set rst = CurrentDb.OpenRecordset("select * FROM  Form6_Crosstab")

    If Not rst.EOF Then
            Do Until rst.EOF
                If IsNull(rst![NAME OF THE COLUMN]) = False Then

                'PY1 = PY1 + rst![NAME OF THE COLUMN]

                End If
        End If

EndDtYr = Format(Forms![IARpts]![EndingDate], "YYYY")

CurrentYear = EndDtYr
Year1 = EndDtYr - 1 (year 1 before the input date)
Year2 = EndDtYr - 2 (year 2 before the input date)
Year3 = EndDtYr - 3 (year 3 before the input date)

EndDtYr is a text field where I enter the current year which the report is based on then I can get the past 3 year in the above code. The problem is the column name in the crosstab changes every time when I enter a new year. How can I refer to the column Names by code. eg. rst![Year1] etc. the name year1 will depends on the year I enter.


Can any one Please help
Question by:mwaresys

Accepted Solution

AlbertoFrog earned 200 total points
ID: 8216457
You can create a string which can refer to the column name.

The recordset object has a fields collection attached to it, but since this is the default property of the object, you don't have to specify it.

E.g. rst("columnName") is equivalent to rst!columnName
(Or rst.Fields("columnName"))

This means that you can build the name in code, rather than fixing it a development time.

Alternatively you can use the same property but pass an integer instead, which will return the field in that ordinal position, starting from 0.

E.g. rst(0) or rst.Fields(0)


Author Comment

ID: 8223862
Thanks AlbertoFrog it worked perfect.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question