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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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: …
Suggested Courses

777 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