variable in field name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

variable in field name

Author

Topic

hjg
Starting Member

2 Posts

Posted - 2009-09-24 : 07:09:40

Hi folks - my first post. Been out of 'Access' for some years and have problem:

SELECT [Avail Query].[First Name], [Avail Query].[Last Name], [Avail Query].[Home Phone], [Avail Query].[Mon am], [Avail Query].[Mon pm], [Avail Query].[Mon eve], [Avail Query].[Tue am], [Avail Query].[Tue pm], [Avail Query].[Tue eve] {and so on}
FROM [Avail Query]
WHERE ((([Avail Query].[Mon am])=Yes));

This code is OK, but if I change the [Mon am] in the WHERE (last line)to a variable eg XXX (so that I can pick any day/period), the parameter box comes up but any entry returns all records. A null naturally returns none. Ideally I would like to select the 'day/period' from a combo box.
Any advice please

Sequin
Starting Member

25 Posts

Posted - 2009-09-24 : 08:38:17

Your data structure does not make it easy - if you had a query header, then child records for the availability it would be muche easier.

The only way to do this is to create an SQL string on the fly, then use this as the source of a recordset - you can then apply the recordset as the recordsource of a form or report - this is off the top of the my head


Dim strSQL AS String
strSQL = "SELECT [Avail Query].[First Name], [Avail Query].[Last Name], [Avail Query].[Home Phone], [Avail Query].[Mon am], [Avail Query].[Mon pm], [Avail Query].[Mon eve], [Avail Query].[Tue am], [Avail Query].[Tue pm], [Avail Query].[Tue eve] {and so on} FROM [Avail Query] WHERE ((([Avail Query].[" & me.cmbSelection & "])=Yes));"

SET Me.Recordsource = strSQL
Me.Requery

hjg
Starting Member

2 Posts

Posted - 2009-09-24 : 09:10:15

Thanks Sequin - as I'm only just starting out and the basic table is fairly small, how do you suggest I re-structure the data?
The table is for volunteers for charity work and it records what days/periods they are available. If I have one field for 'availability' many would have up to 21 entries

As my sql was from a query generated by Access, I'm not sure I undrstand how to use your suggested code.

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources