Results 1 to 6 of 6
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    VBA to Identify a Calculated Field

    Hi,



    I have tables that include calculated fields, which calculate from values of other fields. For example, a FullName field will take the value of first, last and middle names such as:
    Code:
    FirstName & IIf(IsNull(MiddleInit),""," " & MiddleInit) & IIf(IsNull(LastName),""," " & LastName)
    Now I want to take records from my "Changes" table and populate them into my "Real" table. Most fields will be in both tables. So I open a recordset of the "Changes" table and a recordset of the "Real" table. I loop through the "Changes" recordset and for each record I do an AddNew in the "Real" recordset, then loop through the Fields collection in the "Changes" recordset. If the Field is also present in the "Real" recordset, I assign the value from the "Changes" field to the "Real" field.

    So here is the actual question: I want to ignore calculated fields during this process because, well, they're calculated. I've done a bunch of research to find out how to identify a calculated field in VBA, but can't find anything. I've tried the Attributes and Properties items to no avail. It's so easy to see this in the table designer, but I can't figure out how to see this information using VBA.

    Any ideas? Thanks...

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There's a good reason not to use calculated field in table, do calcs in query.

    I don't think you can determine table field type from a query of that table. Think can get properties of table and its fields using TableDefs class. There may be a hidden system table that has these properties. There are some that don't show even when setting Navigation pane to show system tables.

    And now I see Steve has given you a link for TableDefs code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by ssanfu View Post
    Thanks Steve. That solved the issue.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy it helped and you are very welcome.....

    Good luck with your project...

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I have to agree with June7 re: Not using calculated fields in tables especially for something like FullName. Your already storing the first, middle, and last names. Calculate it on demand in your query where needed. A simple way to calculate full names is with the Law of Propagating Nulls.

    Code:
    (FirstName + " ") & (MiddleInit + " ") & (LastName) & (" " + Suffix)
    LOPN basically means a Null plus(+) anything is still null. Therefore if the is no middle initial (MiddleInit + " ") will be null and left out of the result.

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

Similar Threads

  1. How Do I Identify A Field?
    By lccrews in forum Modules
    Replies: 3
    Last Post: 01-04-2018, 05:18 PM
  2. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  3. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  4. Replies: 3
    Last Post: 01-31-2014, 09:40 AM
  5. Replies: 1
    Last Post: 02-03-2011, 11:19 AM

Tags for this Thread

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