Using a variable for a field name (A2K)

Topic

New Reply

I have a lot of virtually identical functions that are used in several different places. These go to a recordset and count the number of records where a particular field is blank. I am trying to make one single generic function within a module that takes as arguments (1) the name of the recordset as a string either as a query or an SQL statement and (2) the field name to be used.

I can set up the recordset from the passed recordset string , e.g. Set rs = db.OpenRecordset(name of query), but I run into a problem when I want to use the field name. The line that causes me a problem is in the form:

If rs!fieldname = “” then …

I can’t see how I can substitute a variable for the name of an actual field. What is the syntax that allows me to do this? I’ve tried but failed to use Eval although that may be because of incorrect syntax.

Reply | Quote

Replies

Try rs.Fields(variable).

Reply | Quote

Try rs.Fields(variable).

Reply | Quote

Instead of rs!fieldname, use rs.Fields(strFieldName) where strFieldName is a string variable that holds a field name.

Reply | Quote

How about in a query?

I’m storing the name of a field in a hidden text box on a form. I want to pass the name of that field to a query. Then that field in the query has criteria. So I want the query to select the field name dynamically based on what is in the hidden text box on the form.

I’ve tried adding this to the field line in a query: [Forms]![Startup]![txtSectionFieldName] but the query returns no results. How do I do this?

Reply | Quote

You can’t use dynamic field names in a query. You could use VBA code to assemble an SQL string dynamically and use that to open a recordset, or even to modify the SQL of a stored query.

Reply | Quote

Okay, that’s what I figured.

I’ll see if I can generate an SQL string. Thanks.

Reply | Quote

Instead of rs!fieldname, use rs.Fields(strFieldName) where strFieldName is a string variable that holds a field name.

Reply | Quote

Thanks folks

Reply | Quote