Help with Dynamic formulas

[UrbaneRove]

Help with Dynamic formulas.

I have a client application the need to have date sensitive formulas for the calculation of some of there reports. We are using MS Access 2000.

The formula stucture could change but it will always use one variable and the rest is numeric. It is designed to get a charge per KM. Then using the calculated number multiple by KMs to get a charge.

ie.
1) (vPriceOfGas * .56) + 4.56
2) (3.05 + vPriceOfGas) * .32

These formulas are to be entered into a table with a date stamp.

I tried a search and replace function that runs dynamically while running all the calculations but as it changes the vba code in the project it also resets my global variables that are needed during this process.

Do you have any idea on how to deal with dynamic formula?

Thanks

 

[lbigk]

Have you considered creating an additional table for your formulas
ex.
Fields: Criteria, Formula
Using Parameter Query, search for Criteria and use a corresponding Formula.


lbigk

 

[BoxHead]

1) (vPriceOfGas * .56) + 4.56
2) (3.05 + vPriceOfGas) * .32

I'm not clear on whether you're entering the above formulas into the table or if vPriceOfGas is the date-sensitive value.

Either way, you should look at the DLookUp, DMax and Eval Functions.

DLookUp("[vPriceOfGas]","tblGasPrice", "[DateStamp] = " & """" & DMax([DateStamp],"tblGasPrice") & """")

should return the most recent value entered in the vPriceOfGas field. (Switch 'tblGasPrice" with your table name and "DateStamp" with the name of your table's Date field.

If the formula itself is stored in the table, use the DLookUp function to call it and try the Eval function with it. I haven't used this function often but it seems like it should work as long as vPriceOfgas is identifiable.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[raskew]

A very interesting problem. Using Northwind's Orders and Order Details tables as a test bed:

(1) Created tblFormula, which looks like this:

dteStart strFormula strDescription
8/1/94 TestA([UnitPrice])
8/10/94 TestB([UnitPrice])

(2) Using your examples, created two functions

Function testA(UnitPrice As Currency) As Currency
   'formula to use when date is from 8/1 - 8/9/94 (see tblFormula)
   testA = ((UnitPrice) * 0.56) + 4.56
End Function
'
Function testB(UnitPrice As Currency) As Currency
   'formula to use when date >= 8/10/94 (see tblFormula)
   testB = (3.05 + (UnitPrice)) * 0.32
End Function

(3) Created code which::
· Evaluated the OrderDate of each record,
· Determined which formula to apply based on the OrderDate
· Created a string incorporating the OrderDate (e.g. "TestA(#8/8/94#)
· Used the Eval function to return the value of the formula applied
against UnitPrice, which was used to represent vPriceOfGas.
· Displayed the output in the debug window.

(4) Using this method it would possible to add additional formulae, just
by adding additional records to tblFormula and creating corresponding
Functions.

(5) If you'd spend a few minutes setting this up (only need to create tblFormula
then copy the code to a new module), you could then test it from the debug
window with: ? myformula(#8/9/94#, #8/25/94#) <enter>

Function MyFormula(enterstartdate As Date, enterenddate As Date) As Currency
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim dteHold As Date
Dim TB, NL, Header, Body, fmt
Dim UnitPrice As Currency, widget As Variant
'
fmt = "$###,###,##0.00"  ' Define money format.
TB = Chr(9) ' Define tab.
NL = Chr(13) & Chr(10)  ' Define newline.
Header = "Order" & TB & "Date" & TB & TB & "Price" & TB & TB & "NewPrice" & Chr(13)
Set db = CurrentDb
strSQL = "SELECT dteStart, strFormula, strDescription" _
    & " FROM tblFormula" _
    & " ORDER BY dteStart DESC;"
Set rs = db.OpenRecordset(strSQL)
strSQL = "SELECT DISTINCT Orders.OrderID, Orders.OrderDate, Products.ProductName, [Order Details].UnitPrice" _
    & " FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID" _
    & " WHERE (((Orders.OrderDate) Between #" & enterstartdate & "# And " _
    & "#" & enterenddate & "#));"
'
Set rs2 = db.OpenRecordset(strSQL)
rs2.MoveFirst
Debug.Print Header
Do While Not rs.EOF
    dteHold = rs2!OrderDate
    rs.MoveFirst
    Do While dteHold > rs!dteStart
       rs.MoveNext
       If rs.EOF Then
          rs.MovePrevious
          Exit Do
       End If
    Loop
    UnitPrice = rs2!UnitPrice
    'build the statement for evaluation
    'note that rs!strFormula is going to return either "TestA()" or "TestB()"
    'next line returns either "TestA(" or "TestB("
    widget = Left(rs!strFormula, InStr(rs!strFormula, "("))
    'next line incorporates the UnitPrice and closes the parenthesis
    widget = widget & UnitPrice & ")"
    'next line is the key to the process since it applies
    'the appropriate formula, based on date and unit price
    widget = Format(Eval(widget), fmt)
    'this prepares a string to be printed in the debug window
    Body = rs2!OrderID & TB & rs2!OrderDate & TB & TB _
         & Format(rs2!UnitPrice, fmt) & TB & TB & widget & TB & Left(rs!strFormula, 5)
    Debug.Print Body
    rs2.MoveNext
    If rs2.EOF Then Exit Do
Loop
rs.Close
rs2.Close
db.Close
Set db = Nothing
'
End Function

 

[UrbaneRove]

Just a note back to say thanks for the replies your help sent me on the right course and I got it all working

Thanks all

UrbaneRove

 

[mbiro]

I am wondering how to store several if...then statements in a table and then call the statement in my vba code (calling the appropriate if..then from aparameter passed)?

Thinking...

a table-

id stmnt
1 if x=1 then...
2 if x=1 then...
3 if x=1 then...

vba -

Public Function artofunction(an8 As Long, cend As Integer)
Dim dbs As Database
Dim rst As Recordset
Dim x As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;SELECT tblcase.fmemo FROM tblaccounts RIGHT JOIN (tblcase RIGHT JOIN tblcasedtl ON tblcase.flngcase = tblcasedtl.flngcase) ON tblaccounts.flngan8 = tblcasedtl.flngan8 where tblcasedtl.flngan8 = &quot; & an8, dbOpenDynaset)
While Not rst.EOF
x = rst!fmemo
rst.MoveNext
Wend

USING THE VALUE X TO USE THE IF...THEN STATEMENT RETURNED


End Function

Is this possible?