Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a variable for a field name (A2K)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using a variable for a field name (A2K)

    Try rs.Fields(variable).
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a variable for a field name (A2K)

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

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable for a field name (A2K)

    Thanks folks

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable for a field name (A2K)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a variable for a field name (A2K)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a variable for a field name (A2K)

    Okay, that's what I figured.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •