How to pass a column name as a parameter in a query

[abhay_547]

Hi Everyone,

I have a table in my database from which I am trying to query the data for a particular month. Now in that database table I have separate column for each month I want to come up with a query which will filter the columns and will give me only one particular month column in the query result. The column name is a combination of Month name and a text .i.e. "_RAG". I have form .i.e. Check RAG Status for All Programs on which I have one command button and a combobox28 the command button is linked to my query and the combobox has list of months for e.g. JAN, FEB, MAR etc. So I want the query to get the value from the combobox of this form and concatenate it with text "_RAG" and then filter for that column from the table Local - RAG Status. Below is what I have so far but it's not working. Please help.

SELECT [Local - RAG Status].ProgramID, [Local - RAG Status].SILO, [Local - RAG Status].PROGRAM_NAME, [Local - RAG Status].MAY_RAG
FROM [Local - RAG Status]
WHERE ((([Local - RAG Status].Forms![Check RAG Status for All Programs]![combo28].Value & "_RAG")="AMBER" Or ([Local - RAG Status].Forms![Check RAG Status for All Programs]![combo28].Value & "_RAG")="RED"));

Thanks for your help in advance

 

[plog]

The issue you posted is not your problem, it is a sympton of your problem. Like a toothache you think you can fix by not chewing on that side of your mouth, there will be more issues and pain in the future if you don't fix the underlying cause: Your database is not properly normalized.

You should not have a column for every month, you should have a field that holds the month the data is for. Instead of [Jan - Rag Status], [Feb -Rag Status], ... [Dec - Rag Status] you have one field to replace them all called something like [MonthOfRagStatus] which holds month values (1, 2, ...12). With a table structured like that, querying the table for just the data you want becomes trivial.

 

[stopher]

As Plog says, you need to normalise your data structure.

The way you have it currently is spreadsheet thinking. But that's not the way databases like Access are designed to work. If you structure like Plog says then the problem you are facing becomes trivial.

ProgramID.........SILO..........Program_Name.......Program_Month......Prog_Qty
123...................xxx...............myProg.....................Jan........................15
123...................xxx...............myProg.....................Feb........................20
etc

Chris

 

[Mr. B]

First let me say that I totally agree that there may be a need to take another look at the normalizaton of your database structure. However, I and say that I have had situations where haveing these type of fieldnames was required.

With that said:
Here is the code that I used in the After Update event of a combo box named "cboFields" to read the value the user has selected and then to create the sql statement that will use other specific columns but only use the one seleced column and then update the "query definition" which acutally rewrites the query.

You will need to change the name of my combobox (cboFields) to the name of your combo box and the "tblMyTest" table name to the name of your table. (I would suggest that you might first want to provide a meaningful name for your controls. This would make it much easier to work with them when writing code.)

Just to be clear, in the code below my combo box is named "cboFields". My table name is "tblMyTest" and the selectable fields in my table are named like: "Jan__RAG", "Feb__RAG", "Mar_RAG", etc. See my comments in this code for what each line does.

Private Sub cboFields_AfterUpdate()
'declare a string type variable to hold the sql statement
Dim strSql As String
'declare a string type variable to hold the selected field name
Dim strSelectedFieldName As String
'assign the selected field name to the variable and 
'concatenate the predefined text value to it
strSelectedFieldName = Me.cboFields & "_RAG"
'create the sql statement using the variable
'to include the user selected Field
strSql = "SELECT tblMyTest.ID, tblMyTest.FName, tblMyTest.LName, " _
& "tblMyTest." & strSelectedFieldName & " FROM tblMyTest;"
'here I am updating the "query definition" of an existin query but
'the sql statement could acutally be used in any appropriate capacity
'use the updated sql statement to actually 
'update the sql part of the predefined query
CurrentDb.QueryDefs("qryTest").SQL = strSql
'additional code as needed
End Sub

 

[abhay_547]

Hi B,

Thanks a lot for your quick reply, I have incorporated the code provided by you in my form and it's working fine but when I try to add some criterias to the same it doesn't work. Below is what I am trying to do .i.e. filter JAN_RAG .i.e. MONTH_RAG column for only AMBER and RED items.

below is the code:

Private Sub Combo26_AfterUpdate()
'declare a string type variable to hold the sql statement
Dim strSql As String
'declare a string type variable to hold the selected field name
Dim strSelectedFieldName As String
Dim Criteria1 As String
Dim Criteria2 As String
Criteria1 = "AMBER"
Criteria2 = "RED"
'assign the selected field name to the variable and
'concatenate the predefined text value to it
strSelectedFieldName = Me.Combo26 & "_RAG"
'create the sql statement using the variable
'to include the user selected Field
strSql = "SELECT [Local - RAG Status].[ProgramID], [Local - RAG Status].[SILO], [Local - RAG Status].[PROGRAM_NAME], " _
& "[Local - RAG Status]." & strSelectedFieldName & " & "FROM [Local - RAG Status] WHERE "[Local - RAG Status]." & strSelectedFieldName & " = " & Criteria1 & " Or "[Local - RAG Status]." & strSelectedFieldName & " = " & Criteria2 & ";"
'here I am updating the "query definition" of an existin query but
'the sql statement could acutally be used in any appropriate capacity
'use the updated sql statement to actually
'update the sql part of the predefined query
CurrentDb.QueryDefs("RAG Status Check Query").SQL = strSql
'additional code as needed
End Sub

The above code is not working it is showing the error on the sql statement line. Please help.

Thanks a lot for your help in advance.

 

[Mr. B]

From the VBA code window, place a breakpoint just below the line of code that assigns the sql statement to the strSql string variable. Then open the Immediate Window an type ?strSql

Copy the results of that to the clipboard and then create a new query, switch to sql view and paste in the sql statement you just copied. Try to switch to design view and you may be able to see where your statement is not being concatenated correctly. Then just make the necessary corrections to how you are doing the concatenation when createing the sql statment.

 

[abhay_547]

Hi B,

I am bit confused with the solution which you have posted. Can you please help me with the below sql statement.

strSql = "SELECT [Local - RAG Status].[ProgramID], [Local - RAG Status].[SILO], [Local - RAG Status].[PROGRAM_NAME], " _
& "[Local - RAG Status]." & strSelectedFieldName & " & "FROM [Local - RAG Status] WHERE "[Local - RAG Status]." & strSelectedFieldName & " = " & Criteria1 & " Or "[Local - RAG Status]." & strSelectedFieldName & " = " & Criteria2 & ";"

Thanks for your help in advance.

 

[Mr. B]

From your origiinal post, you indicate that the Criteria1 and Criteria2 variables are string type variables. If this is the case then you must have the syntax correct for specifying a string type variable in an expression. That syntax is: '" & Criteria1 & "' (Notice the starting and ending single quote)

Using my original table names etc, take a look at this assignment of the sql statment to the strSql variable:

strSql = "SELECT tblMyTest.ID, tblMyTest.FName, tblMyTest.LName, " _
       & "tblMyTest." & strSelectedFieldName & " FROM tblMyTest " _
       & "WHERE (((tblMyTest." & strSelectedFieldName & ")='" & strCriteria1 & "')) " _
       & "OR (((tblMyTest." & strSelectedFieldName & ")='" & strCriteria1 & "'));"

Below I have tried to correct your statement.

strSql = "SELECT [Local - RAG Status].[ProgramID], [Local - RAG Status].[SILO], [Local - RAG Status].[PROGRAM_NAME], " _
& "[Local - RAG Status]." & strSelectedFieldName & " FROM [Local - RAG Status] " _
& "WHERE [Local - RAG Status]." & strSelectedFieldName & " = '" & Criteria1 & "' " _
& "Or [Local - RAG Status]." & strSelectedFieldName & " = '" & Criteria2 & "';"