MS Access accepting column name as parameter

[Sparta71]

Hello,

I'm pretty new to this, but I'm working on an MS Access DB and I'm trying to create a query that will accept a column name as a parameter. The table is set up to show an employees base schedule and there is a seperate column for each hour of work. If the column has a value of 1, they're scheduled to work... if the value is 0 they aren't. We would like one query that can be used to return who is working for the given hour, but the problem is that the column names are named like h1, h2, h3, etc. and I can't figure out how to accept the column name as a parameter.

Generally I can do something like [@parametername] but that doesn't seem to work for column names. I also tried tablename.[@hour] but no luck there either.

Thanks!

P.S. Not sure if it matters but it's MS Access 2000

[ADezii]

Hello,

I'm pretty new to this, but I'm working on an MS Access DB and I'm trying to create a query that will accept a column name as a parameter. The table is set up to show an employees base schedule and there is a seperate column for each hour of work. If the column has a value of 1, they're scheduled to work... if the value is 0 they aren't. We would like one query that can be used to return who is working for the given hour, but the problem is that the column names are named like h1, h2, h3, etc. and I can't figure out how to accept the column name as a parameter.

Generally I can do something like [@parametername] but that doesn't seem to work for column names. I also tried tablename.[@hour] but no luck there either.

Thanks!

P.S. Not sure if it matters but it's MS Access 2000

__1 Create a Query called qryBaseSchedule which contains all your Fields (Name, h1 to h24, etc.) with no criteria.
__2 Name your Table tblBaseSchedule or adjust code.
__3 Create an Unbound Combo Box on a Form called cboHour and simply place the Hours in its single column (h1, h2, h3, h4, h5, h6, h7...).
__4 In the AfterUpdate Event of the Combo Box, place the following code:

Private Sub cboHour_AfterUpdate()
   CurrentDb.QueryDefs("qryBaseSchedule").SQL = "Select * FROM " _
             & "tblBaseSchedule Where [" & Me![cboHour] & "] =" & 1
   DoCmd.OpenQuery "qryBaseSchedule"
End Sub

__5 Now, whenever you select an Hour in the Combo Box, all Records that contain a 1 in that specific Hour Field will be returned.
__6 There may be an easier Method, but it doesn't exactly come to mind right now. Any questions, feel free to ask.

[Sparta71]

Thanks for the reply. I'm still having some trouble and perhaps it will help if I give more background information.

Basically we have an ASP.Net/C# application that connects to an Access DB. There are about 100 queries currently stored in the Access DB and there are no problems calling and using them. However, there is a table that was poorly structured and the only way I can create a query that will do what I want is to pass a column name as one of the parameters (h1, h2, etc.). Is there any way to do that in Access? I keep reading through the Access help guides and nothing has worked.

I know that I could make 24 seperate queries and call those seperately, but that seems tedius and inefficient. I'm also thinking that I could just imbed the SQL in the application but I guess that's a last resort. Again, I'm pretty new to this and my main goal is to not break anything that's already working.

Thanks!

[ADezii]

Thanks for the reply. I'm still having some trouble and perhaps it will help if I give more background information.

Basically we have an ASP.Net/C# application that connects to an Access DB. There are about 100 queries currently stored in the Access DB and there are no problems calling and using them. However, there is a table that was poorly structured and the only way I can create a query that will do what I want is to pass a column name as one of the parameters (h1, h2, etc.). Is there any way to do that in Access? I keep reading through the Access help guides and nothing has worked.

I know that I could make 24 seperate queries and call those seperately, but that seems tedius and inefficient. I'm also thinking that I could just imbed the SQL in the application but I guess that's a last resort. Again, I'm pretty new to this and my main goal is to not break anything that's already working.

Thanks!

To the best of my knowledge, there is no way to pass a Column Name as a Parameter in the Query Grid but I could be wrong. The only Method I know is what I previously had Posted (a combination of an SQL Statement and VBA code). Does it necessarily have to be a Stored Query?

[NeoPa]

If I can't come up with a better solution (which I certainly can't atm) then you can use a technique 'similar' to ADezii's, in that it uses the SQL of an already designed QueryDef but simply modifies it for specific use.
You don't say what you want the query for so I can simply show how to set up the SQL.
Your qryBaseSchedule query should be set up to assume we always want to use [H1].

Dim strSQL As String
strSQL = CurrentDb.QueryDefs!qryBaseSchedule.SQL
strSQL = Replace(strSQL, "[H1]", "[H" & Me!cboHour & "]")