Results 1 to 4 of 4
  1. #1
    Tony Franks is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    2

    How can I refer to field names using a variable

    In a programming language like Clipper you can code




    for i = 1 to 12
    ai=str(i)
    replace Sales&ai with 0
    next i


    where the fields in a table are called Sales1, Sales2, Sales3, etc


    How can this be done in Access?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for controls in a form or report called Sales1, Sales2 etc you would use

    Code:
    for i = 1 to 12
       me("Sales" & i)= 0
     next I
    with a table in VBA, you need a recordset and your code would be something like

    Code:
    dim rst as dao.recordset
    set rst=currentdb.openrecordset ("SELECT * FROM myTable")
    
    if not rst.eof then
        with rst
        for I=1 to 12
            .fields("Sales" & I)=0
        next I
    end if

    .

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Ajax's answer to your specific question.
    However, if you have a table with Sales1, Sales2...SalesX, you may want to review your table or processing. This typically signals an un-normalized table structure. Relational databases use Normalized tables.
    See this on Normalization -repeated columns.

  4. #4
    Tony Franks is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    2
    Thanks Ajax.
    The VBA/Recordset solution works fine (after a bit of trial and error!!!)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use a string to refer to a variable
    By kdbailey in forum Access
    Replies: 5
    Last Post: 12-15-2015, 10:50 PM
  2. Import Procedure - Variable File Names
    By Ada01 in forum Programming
    Replies: 5
    Last Post: 03-03-2015, 05:43 PM
  3. Replies: 4
    Last Post: 02-16-2012, 05:23 PM
  4. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM
  5. Variable Table Names in a Module
    By Jessica in forum Modules
    Replies: 1
    Last Post: 04-19-2010, 07:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums