Creating Dynamic IF Statements for Selective Record Groupings

[statmanron]

In MS Access - VBA, I'm trying to group records while looping through a recordset. Is there a way I can create two dynamic if statements(1. condition to start the occurrence/looping, 2. end the occurrence) using fields within an Access form. For instance, I've created an MS Access form where a user can select a table's field name within a combo box, another field that lists conditional operators (+,-,/,*) an another field where a user has a choice whether the second value will be another field name or a random value. Is there a way to create dynamic if statements to avoid overloading numerous functions???

for example - If "analyze this dynamic statement" then

I know some of you will suggest creating a dynamic statement within the criteria of the sql statement but I dont want to do that because I want to loop the entire recordset. In addition, I would still require creating a dynamic 'if' to end the respective grouping/occurrence.

Would literals be considered?

Thank you

 

[Andrzejek]

The If statement evaluates True/False conditions, so you need to make your analyze-this-dynamic-statement return True or False:
[tt]
If [red]analyze-this-dynamic-statement[/red] then
[/tt]
I don't think you can use a string, but you can check the Len of the string to be evaluated as True (Len(str) > 0) or False (Len(str) = 0)

The best variable to use is a Boolean


Have fun.

---- Andy

 

[MajP]

Instead of telling us how you want to do it explain what you are attempting. The fact you feel compelled to loop a recordset, is really not a reason to do it. Just clearly explain what you have and what results you desire, and we can provide a more logical approach. If you are considering creating dynamic code, either your database is not properly formed, your user interface is not properly designed.

 

[statmanron]

Fair enough MajP. I've created a stock table that stores historical prices and some other calculate fields. In order to back test certain scenarios, I would like create a user interface to select field names from the table and conditions (">", "<", "=") to compare with other fields or random values.
Thanks

 

[MajP]

I would like create a user interface to select field names from the table and conditions
(">", "<", "=") to compare with other fields or random values.

That reads as if you simply want to create a where statement.
PE > 5
or
EstPrice > ActualPrice

So could you give an example of something you would create, and what you mean by compare and group. Why can you not build the recordset to do the grouping? What would you return?

 

[statmanron]

As example would be ClosingPrice > MA20(20 day moving avg), I would like to group/calculate totals (Business day counter, on going gains) until ClosingPrice > MA20 and ClosingPrice > MACD(another indicator). Sometimes there would be numerous conditions.
Thanks!

 

[SkipVought]

Sometimes there would be numerous conditions.

And how would that be 1) identified as multiple and 2) known condition values?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[statmanron]

Multiple conditions could exist with the (1) initial/starting criteria and with (2) finalizing criteria. All fields are listed within the table.

 

[MajP]

So you are always traversing these in sequential order. You want to stop once the first closing Price < MA20 or ClosingPrice < MACD.

So what I have to find is the first date to kick out, which in turn corresponds to all records less than that min date.

So All I have to do is find the minimum kick out date.

Imagine I have a way to get lots of conditions. My example uses the Nortwind database. One is comparing two fields one is a literal

Public Sub TestFindMin()
  Dim conditions As New Collection
  Dim stopDate As Date
  'some code to fill your conditions
  'A condition is the first date something comes true
  conditions.Add ("RequiredDate = ShippedDate")
  conditions.Add ("Freight = 7.45")
  
  stopDate = FindStopDate(conditions)
  Debug.Print stopDate
End Sub
['code]

I can pass all these conditions to a generic function to find the first date that one of these comes true. If it was an or condition you could have another functions to return the first date when both come true

[Code]
Public Function FindStopDate(conditions As Collection) As Date
  Dim condition1 As String
  Dim MinOrderDate As Date
  Dim TempOrderDate As Date
  Dim I As Integer
  For I = 1 To conditions.Count
    Debug.Print conditions(I)
    TempOrderDate = DLookup("OrderDate", "qryOrders", conditions(I))
    Debug.Print TempOrderDate
    If I = 1 Then
      MinOrderDate = TempOrderDate
    Else
      If TempOrderDate < MinOrderDate Then
        MinOrderDate = TempOrderDate
      End If
    End If
  Next I
    FindStopDate = MinOrderDate
End Function

Now I do not have to loop anything. I pass this date to my group by query and it can return the totals up to the point where
RequiredDate <> ShippedDate and Freight <> 7.45

Now I did not show the starting conditions, but you can figure that out. My start and stop would eventually be two dates with all records between that date.

 

[MajP]

Let me say that a little differently. You can find the first record that meets the starting conditions. You can find the first record that meets the stopping conditions. Pass those two dates to a group by query and return the results for all records between the start date and end date.

 

[statmanron]

That is a great alternative MajP - you are awesome!
Its just too bad we cant send a conditional "STRING FORMATTED CONDITION" to an if statement like the following:

Read record
Initialize/Setup New StatStudy
do until eof

If "STRING FORMATED CONDITION" then
setup the SS object
loop
SS!Businesday = SS!Businessday + 1
if SS!Businessday = 1
SS!StartDate = myrst!Date
SS!StartClose = myrst!Close
.....addiional calculatations
elseif SS!BusinessDay = 2
SS!PercentageHigh = SS!
Else
apl_percentage = myrst!Close - SS!StartClose / SS!StartClose
if apl_percentage > SS!PercentageHigh then
SS!PercentageHigh = apl_percentage
SS!PercentageHighDate = myrst!Date
SS!PercentageHighBusinessdays = BusinessDayCounter
endif
if apl_percentage > SS!PercentageLow then
SS!PercentageLow = apl_percentage
SS!PercentageLowDate = myrst!Date
SS!PercentageLowBusinessdays = BusinessDayCounter
endif
endif
rst.movenext

do until "STRING FORMATED CONDITION" or rst.EOF

Write the contents of the object to a temp table
else
rst.movenext
endif

loop

 

[MajP]

You actually can, but I still would not recommend doing it that way. I think some version of what I suggest would be easier and more efficient. But if you really would want to do that you could actually write dynamic code

http://www.cpearson.com/Excel/vbe.aspx

 

[PHV]

Did you consider the Application.Eval method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[statmanron]

PHV - Eval is the best function in the world. Thank you. This prevents the need to overload numerous functions. You're awesome.
MajP - You're awesome too!!!

Thanks