Filter based on Date

[Ebes1099]

I have a form created with a Subform on it, the main form displays a Code, then the Subform displays an associated fee for different fee lists for that code. The subform is based on a table called Fee Schedules. In that table I have the fields, Code, Fee List, Fee, Discount, EffectiveDate. There are multiple records for each code because there are different Fee Lists where the Code can have different Fees. So the continuous form shows the fee in each fee list.

What I want is for the user to be able to enter a Date on the main form, and it will only show the fees that cover that date. Since next year, new fees will be entered to that table with new effective dates I won't want to show all the fees for that code. I only want to show the Fees that are effective at the date the user enters. So if the user enters 6/30/09, it needs to look back and find the records in the table that match that code, and have the most current effective date? Any suggestions on how I can do this? Thanks.

 

[dhookom]

Please provide some actual sample records and desired display in your form.

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

On the main form, I am displaying the Code, then a text box where the user can input a date. (I also have a search feature where the user can type in a code and it will go to that record)

Then in the subform, It shows Fee Schedule, Price, Discount, Effective date. This continuous form has usually 5-6 records. An example would be Code 1234 on the main form, then in the subform I would have

Fee Schedule A $50 0 1/1/2009
Fee Schedule B $55 0 1/1/2009
Fee Schedule C $60 0 1/1/2009
etc...

None of the fields are editable by the user. I just want them to be able to look at them. (I know I could do this in a report, but the users want a form)

So if the user wants to see code 5678, and the fee for the date of 6/30/2009, it should show all the fee schedules that are in effect on that date. The date the user enters though won't match the effective date in the table. I'll have to do some sort of check to find the record that covers the date entered.

Dhookom, you might have meant something else when you said "actual sample records" and if you need something else/more let me know. Thanks for the help.

 

[dhookom]

I would have expected to see some records that matched and didn't match with an indication of which records to display. Your records should all match since their effective date is prior to 6/30/2009.

Generally, I would write some code to change the Record Source of the subform like:

Dim strSQL As String
Dim strWhere as String
strWhere = " WHERE 1 = 1 "
strSQL = "SELECT [Fee Schedule], [Price], [Discount], [Effective date] " & _
    "FROM ... "
If Not IsNull(Me.txtEffDate) Then
    strWhere = strWhere & " AND [Effective Date] <=#" & _
        Me.txtEffDate & "# "
End If
Me.sfrmFeeSchedules.Form.RecordSource = strSQL & strWhere

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

There are records that don't match, but right now they only don't match the criteria based on the Code. I only have 1 years worth of fee schedules in the table right now since I just built this database for use this year. Next year, new fee schedules will be uploaded into the table, with effective dates of 1/1/2010. (Typically fees are set at the beginning of the year so they'll have a 1/1 effective date, but they can change mid year).

So if I change the record source on my Subform it will remove my Link to the master form I have with the "Code" field. I'm assuming I'll just have to include that in my Where statement. I'm thinking instead of having any bound fields on the main form, I can just have 2 user input fields where the user can enter a Code and a Date and I'll use those in my Where statement. Thanks Dhookom.

 

[dhookom]

Aren't you using the Link Master/Child for the Code field? If you aren't does changing the Record Source remove the link?

I expect the effective schedule will go out of date when a new one takes effect.

Duane
Hook'D on Access
MS Access MVP