Clever Geek Handbook

Dynamic calculated field in VBA access

I am trying to add a field to an existing table. The field is calculated using two variables "myPrice", which is the price of the entry on a specific date, and "previous price", which is the price of the same portion from the first entry, only from the previous month. I am using a loop to iterate over the entire set of price records, thus changing two variables each time. My code looks like this:

Function priceDifference()

Dim currentPrice As Currency
Dim previousPrice As Currency
Dim recordDate As Date
Dim previousDate As Date
Dim dbsASSP As DAO.Database
Dim priceTable As DAO.TableDef
Dim diffFld As DAO.Field2
Dim rstCurrentPrice As DAO.Recordset
Dim rstPreviousPrice As DAO.Recordset
Dim PN As String

Set dbsASSP = CurrentDb
strSQL = "SELECT * FROM qryPrice"
Set rstCurrentPrice = dbsASSP.OpenRecordset(strSQL, dbOpenDynaset)

Set priceTable = dbsASSP.TableDefs("tblPrice")
Set diffFld = priceTable.CreateField("Difference", dbCurrency)

If Not (rstCurrentPrice.EOF) Then
    rstCurrentPrice.MoveFirst
    Do Until rstCurrentPrice.EOF = True
        PN = rstCurrentPrice![P/N]
        recordDate = rstCurrentPrice![myDate]
        previousDate = Format(DateAdd("m", -1, recordDate), "M 1 YYYY")
        myPrice = rstCurrentPrice!Price
        strPreviousSQL = "SELECT * FROM qryPrice WHERE [MyDate] = #" & previousDate & "# AND [Type] = 'Net Price' AND [P/N] = " & PN & ""
        Set rstPreviousPrice = dbsASSP.OpenRecordset(strPreviousSQL, dbOpenDynaset)
        myCodeName = rstCurrentPrice!CodeName
        If DCount("[P/N]", "qryPrice", "[MyDate] = #" & previousDate & "# And [P/N] = " & PN) <> 0 Then
            previousPrice = rstPreviousPrice!Price
        Else
            previousPrice = myPrice
        End If

    rstCurrentPrice.MoveNext
    Loop
Else
    MsgBox "Finished looping through records."
End If

diffFld.Expression = myPrice - previousPrice

rstCurrentPrice.Close
rstPreviousPrice.Close

priceTable.Fields.Append diffFld

End Function

Syntactically it works. The calculated field, however, doesn't give me the correct values, and I can't figure out why, although I think it has something to do with the dynamic formula.

Any help is appreciated. Thank!!

+3


source to share


2 answers


Your code may not work as you are adding a number to a field instead of a formula, but you should avoid calculated fields

Use query:

SELECT 
    nPrice.[P/N], 
    nPrice.Price,  
    nPrice.MyDate,
    oPrice.MyDate,
    nPrice.Price-Nz(oPrice.Price,nPrice.Price) AS diff
FROM (
   SELECT 
       [P/N],
       Price, 
       MyDate, 
       Format(DateAdd("m",-1,MyDate),"yyyymm") as previousMonthYear
   FROM 
       tblPrice) AS nPrice 
LEFT JOIN (
    SELECT 
        [P/N], 
        Price, 
        MyDate,
        Format(MyDate,"yyyymm") AS monthYear 
    FROM 
        tblPrice)  AS oPrice 
ON 
    nPrice.[P/N] = oPrice.[P/N] 
    AND nPrice.previousMonthYear = oPrice.monthYear


This calculates the dynamics of the difference and you don't need to store it.

I am assuming there is a maximum monthly price, otherwise you need to filter the subqueries or just calculate the difference to the last price. If the query is too slow ([P / N] and MyDate needs an index), you can still use it to update the table field tblPrice

, but this needs to be updated every time the price is updated or inserted intotblPrice

+1


source


I think you can avoid the code loop by using this query



SELECT A.qryPrice, A.MyDate, B.qryPrice, B.MyDate
FROM qryPrice A LEFT OUTER JOIN qryPrice B 
ON A.[P/N] = B.[P/N] 
AND B.MyDate = DATEADD(month, -1, A.MyDate)
+1


source






More articles:


All Articles