How to populate fields in table with VBA?

[MsAxes]

Hi all - I have a query where I have too many iif statements where I run into the max statements allowed and have to continue in a second/third column that ends in calling the results from the 1st two columns. I would like an example of a vba statement that looks at several fields in a table and if that condition is met to return specific text in an empty column that table has.

Ex. if column A, B, C in table "My_Data" all equal ST2500 then I would like a column result to be "Prior 2010"
if column A and B = TQ81 and column C = 152 then the column should populate with 2015 and so on and so forth.



I just need an example to get me started.

[Gicu]

Usually you don't "populate" a table's field with a calculation as you can quickly loose data integrity if one of the three columns changes. To display the value that you would store in the table you use a form or a query. In both the result would be calculated "on the fly" by a custom VBA function in which you would use nested Select or If statements to get your value.

Cheers,

[MsAxes]

I forgot to mention that this table is updated daily so the values are expected to change - i found an example that appears to be what I need.

Option Compare Database

Sub CreateCalculatedField()
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field2
        
        ' get the database
        Set dbs = CurrentDb()
        
        ' create the table
        Set tdf = dbs.TableDefs("My_Data")
          
        ' create the calculated field: full name
        Set fld = tdf.CreateField("Test_Column", dbText, 20)
        fld.Expression = "[FirstName] & "" "" & [LastName]"
        tdf.Fields.Append fld
        
        ' append the table and cleanup
        dbs.TableDefs.Append tdf
        
Cleanup:
        Set fld = Nothing
        Set tdf = Nothing
        Set dbs = Nothing
    End Sub

The next guidance I need is how could I start writing my if statement in the fld.Expression section? I just need to see an example and I feel that I can complete this task. Note that I will prob. have 15-18 iif statements.

[Gicu]

That code doesn't do anything near to what your initial request is. In the original post you mention a query (a update one) that will update an existing field in a table based on three other fields in the same record. The code you show will add a new calculated field to a (new) table every time it is run.
Here is some info about calculated fields in tables and why you should avoid them:http://allenbrowne.com/casu-14.html

To solve your problem you would simply write a public VBA function in a standard module that returns your calculation and use that in a calculated field in a query:

 
Public Function fnCalculateField(sField1 as string,sField2 as String, sField3 as String) as String


Select Case sField1
Case "ST2500"
     Select Case sField2
     Case "ST2500"
         Select Case sField3
         Case "ST2500"
              fnCalculateField="Prior 2010"
         Case.....
          ...........
          End Select
     Case "TQ81"
         Select Case sField3
         Case .......
              fnCalculateField=.......
         Case.....
               .............
         End Select
	End Select
Case "TQ81"
	Select Case sField2
     Case "ST2500"
         Select Case sField3
         Case "ST2500"
              fnCalculateField=.....
         Case.....
          ...........
          End Select
     Case "TQ81"
         Select Case sField3
         Case "152"
              fnCalculateField="2015"
         Case.....
               .............
         End Select
	End Select
........................
End Select

And in the query you would add a field CalcField:fnCalculateField([A],[B],[C])

Cheers,

[Bob Fitz]

I agree with the advice given by Gicu that you should NOT usually store calculated fields. Ignore it at your peril.