Filter Subform by Combo Box Dates

[steve042]

Hello,



Currently I have a main form w/ subform. In the main form, a user selects a bond's serial number from a combo box and the subform displays the bond's returns for the months owned along with a start date (each month has its own start date). So if I enter bond ABC in combo box, the subform will display "13-01-01, 4%" for January, "13-02-01, 8%% for February..etc.


So if we owned a bond from January to March, it will display the three months' returns. I want to add two combo boxes: begin and end date so that I can filter the subform to only show a particular set of months' returns.

I have three tables right now: Cusips, which lists each bond's serial number, CusipStartPK, which lists the monthly returns for each serial number, and ParameterDates, which is a list of four start dates--ie 13-01-01 for January, 13-02-01 for Feb, etc.

I've tried parameter queries and vba codes from other threads but I can't get this to work. Any help would be greatly appreciated and I can answer any further questions.

[rpeare]

I have a question.

Do the bonds have a fixed rate of return or is it variable and is the rate compounded from month to month or year to year?

If it's fixed you don't need a secondary table at all you can figure the return at any point in the bond's life.
If the rate can vary from month to month and that's what you're recording (if the 4% represents the return from 12/2/2012 through 1/1/2013 and 8% represents the return from 1/2/2013 through 2/1/2013 as opposed to a flat rate that is compounded yearly where you'd expect a flat percent increase per month which would double from jan to feb)

At any rate there are a few ways you can attack this problem

To me, the easiest is to change the ROWSOURCE for the subform.

So for instance, let's say your 'default' subform has a rowsource of "SELECT * FROM CUSIPS"

when you click your 'search' button you'd have something like:

if not isnull([Startdate]) and not isnull([EndDate])
    me.subform.rowsource = "SELECT * FROM CUSIPS WHERE [DateField] between #" & [StartDate] & "# AND #" & [EndDate] & "#"
else
    me.subform.rowsource = "SELECT * FROM CUSIPS
endif

so that if a start and end date are entered it will be filtered by that date otherwise it shows the 'default' list.

I'm not 100% sure I have the syntax for changing a subform rowsource correct but if it's not it should be easy to find.

[steve042]

I have a question.

Do the bonds have a fixed rate of return or is it variable and is the rate compounded from month to month or year to year?

If it's fixed you don't need a secondary table at all you can figure the return at any point in the bond's life.
If the rate can vary from month to month and that's what you're recording (if the 4% represents the return from 12/2/2012 through 1/1/2013 and 8% represents the return from 1/2/2013 through 2/1/2013 as opposed to a flat rate that is compounded yearly where you'd expect a flat percent increase per month which would double from jan to feb)

At any rate there are a few ways you can attack this problem

To me, the easiest is to change the ROWSOURCE for the subform.

So for instance, let's say your 'default' subform has a rowsource of "SELECT * FROM CUSIPS"

when you click your 'search' button you'd have something like:

if not isnull([Startdate]) and not isnull([EndDate])
    me.subform.rowsource = "SELECT * FROM CUSIPS WHERE [DateField] between #" & [StartDate] & "# AND #" & [EndDate] & "#"
else
    me.subform.rowsource = "SELECT * FROM CUSIPS
endif

so that if a start and end date are entered it will be filtered by that date otherwise it shows the 'default' list.

I'm not 100% sure I have the syntax for changing a subform rowsource correct but if it's not it should be easy to find.

The bonds' return changes each month, I should have used a better example looking back at it now.

[rpeare]

So did you try changing the rowsource and did it work?

[steve042]

So did you try changing the rowsource and did it work?

I ended up changing the record source in the subform to include the criteria from the combo box and it ended up working. The last thing I'm trying to work on for this form is creating a text box to show the geometric average of the monthly returns from the subform. Basically I want to do [(January Return/100)+1]*[(February Return/100)+1]...so on and so forth. Right now I can get the first record's calculation to show up in my text box by just putting

=([MonthlyReturn]/100)+1

in the control source box. In my brief searches it looks like I may need some sort of vba code which is completely over my head.

[rpeare]

are you talking about printing the geometric average on a report, or are you trying to show it on a query/form?

and as I understand it your original data is like this:


1/31/2013 4%
2/28/2013 8%
3/31/2013 6%


and so on

so what you want is this:

1/31/2013 4% 1.0004 (I'm assuming you're storing the percent as a decimal and not as a text value)
2/28/2013 8% 1.00120032
3/31/2013 6% 1.001801040192

is that correct?

[steve042]

are you talking about printing the geometric average on a report, or are you trying to show it on a query/form?

and as I understand it your original data is like this:


1/31/2013 4%
2/28/2013 8%
3/31/2013 6%


and so on

so what you want is this:

1/31/2013 4% 1.0004 (I'm assuming you're storing the percent as a decimal and not as a text value)
2/28/2013 8% 1.00120032
3/31/2013 6% 1.001801040192

is that correct?

I'm trying to show it via a text box on the form (presumably below the subform) that will show 1.19 (The result of 1.04*1.08*1.06). I only need the calculation for the entire scope of the bond's ownership.

[rpeare]

So you are not storing your percentages as decimals then.

The short answer is yes, you'd likely to need to get vba (script) involved to give you the result you wanted.

how are you storing your percentage is it a number like 4.5 instead of .045
or are you storing it as a text value so that you can type in the %?

[steve042]

So you are not storing your percentages as decimals then.

The short answer is yes, you'd likely to need to get vba (script) involved to give you the result you wanted.

how are you storing your percentage is it a number like 4.5 instead of .045
or are you storing it as a text value so that you can type in the %?

I am storing it as a number like 4.5

I don't actually have the % in because the percentage is assumed by my manager for now.

Thanks for your help rpeare.

[rpeare]

Here's a quick example of how to do it by cycling through a recordset

Steve042.zip

just click the button (I didn't error check for null/0 values etc, there's one record that has some funding information and the code works. You can apply the method to wherever you want to update that percentage, for instance in the after update or on exit property of one of the data entry fields in the subform.

[steve042]

It worked. Thank you so much.

[steve042]

Quick question

Right now I have my VBA code as

Private Sub cmdCalculate_Click()Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim dCurrPct As Double
Dim dNetPct As Double


Set db = CurrentDb
Debug.Print Forms!Security!Combo15.Column(0)
sSQL = "SELECT * FROM CusipStartPK WHERE Cusip_ID = '" & Forms!Security!Combo15.Column(0) & "' ORDER BY Start_Dt"
Set rst = db.OpenRecordset(sSQL)
dNetPct = 1
If rst.RecordCount <> 0 Then
    Do While rst.EOF <> True
        dCurrPct = rst.Fields("TRR_%_MTD_LOC")
        Debug.Print dCurrPct
        dNetPct = dNetPct * (dCurrPct + 1)
        rst.MoveNext
    Loop
End If


NetPct = dNetPct
rst.Close
Set db = Nothing
End Sub

Which gives the geometric return of all months' returns. I'm pretty sure I need to add two more WHERE conditions so that the start and end dates can be filtered (IE show the geomtric return of just January through March instead of all five months). Basically, I'm trying to take these two parameter queries from the subform recordsource and add them to the ssql statement.

Start_Dt >=[Forms]![Security]![CboBegDate] 
End_Dt <=[Forms]![Security]![CboEndDate]

[rpeare]

sSQL = "SELECT * FROM CusipStartPK WHERE Cusip_ID = '" & Forms!Security!Combo15.Column(0) & "' AND Start_Dt >= #" & [forms]![security]![cboBegDate] & "# AND End_Dt <= #" & [forms]![security]![cboBegDate] & "# ORDER BY Start_Dt"

[steve042]

sSQL = "SELECT * FROM CusipStartPK WHERE Cusip_ID = '" & Forms!Security!Combo15.Column(0) & "' AND Start_Dt >= #" & [forms]![security]![cboBegDate] & "# AND End_Dt <= #" & [forms]![security]![cboBegDate] & "# ORDER BY Start_Dt"

It gives me 1 as the geometric return for every security regardless of dates. My dates are in text format, does that matter?

[steve042]

rpeare, I just had to change the # to ' in the code. Thanks for your help again.