Urgent - Filter Sub Form in VB by button Filter Property

[Phred]

Access 2007, not compiled, not split, Windows 7, Filter Property, I have never used filtering before, VBA skill levels moderate.

I have a main form where the end user will schedule several hundred meals, or thousands, by date, usually a month at a time. The meals appear in the sub form once they are scheduled.



The user might be in the middle of scheduling meals for a particular month and get interrupted. When they come back there are so many records they have a hard time finding where they left off. They want to be able to filter the sub form by date range. They would be filtering against the date field in the sub form "DateAssigned".



As the sub form shows they started scheduling for March. But there may be 1,000 records and it is hard to find where they left off. What I need to do:

The end user selects a date range in the two fields that they want to filter for. They click Filter Menus button. My code is a guess. The Filter Property in help or articles is not clear for me. Here is my code so far:


Private Sub CmdFilterMeals_Click()

Dim FilterStartDate As Date, FilterEndDate As Date

FilterStartDate = Me.FilterDateStart

FilterEndDate = Me.FilterDateEnd

'Filter The Sub Form for DateAssigned between FilterStartDate and FilterEndDate. The nex line is what I need help with.

me.filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= "# & FilterEnddate & ");"'"

'It could also be a between statement.

Me.Filter = True

I have read every post I can find but nothing is clear so far.

I have never asked for quick help before but it would be really appreciated.

Phred

[pbaldy]

You got the # delimiters around the first date value correct, not so much around the second.

[Phred]

Wow! I was actually that close? Astounding! I must actually be learning.

Ok, I implemented the corrected code below. On click it executes without any error, but the sub form does not filter. My assumption is I am not addressing the DateAssigned field in the subform correctly or I am still missing something that has the subform show the filtered state. With the two records showing on the .Jpg I filtered for 3/1/2012 - 3/1/2012. It should have filtered to just one record but it still shows the two records. If I select a date range in February the records still show and they should vanish.

Dim FilterStartDate As Date, FilterEndDate As Date
FilterStartDate = Me.FilterDateStart
FilterEndDate = Me.FilterDateEnd
Me.Filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= #" & FilterEndDate & "#);"
Me.Filter = True

Me.Refresh 'does not work
Me.Requery 'does not work

Thanks again Fred

[pbaldy]

You're overwriting the filter with the second line. It should be

Me.FilterOn = True

[pbaldy]

By the way, I don't think you want the ");" at the end of the filter

[ssanfu]

Wow! I was actually that close? Astounding! I must actually be learning.

Ok, I implemented the corrected code below. On click it executes without any error, but the sub form does not filter. My assumption is I am not addressing the DateAssigned field in the subform correctly or I am still missing something that has the subform show the filtered state. With the two records showing on the .Jpg I filtered for 3/1/2012 - 3/1/2012. It should have filtered to just one record but it still shows the two records. If I select a date range in February the records still show and they should vanish.

Dim FilterStartDate As Date, FilterEndDate As Date
FilterStartDate = Me.FilterDateStart
FilterEndDate = Me.FilterDateEnd
Me.Filter = "[DateAssigned] >= #" & FilterStartDate & "# AND [DateAssigned] <= #" & FilterEndDate & "#);"
Me.Filter = True

Me.Refresh 'does not work
Me.Requery 'does not work

Thanks again Fred

So close!!
Once you set the filter string, you have to turn on the filter.....

The example from Help:
Me.Filter = "Country = 'USA'"
Me.FilterOn = True

Dim StartDate As Date, EndDate As Date
StartDate = Me.FilterDateStart
EndDate = Me.FilterDateEnd
Me.Filter = "[DateAssigned] >= #" & StartDate & "# AND [DateAssigned] <= #" & EndDate & "#;"
Me.FilterON = True

NOTE: I renamed your variables for the dates. I don't like having a variable in code with the same name as a control on a form or field in a table/query. Less confusion if they have different names.

To remove a filter:

Me.Filter = "" 
Me.FilterOn = FALSE

[Phred]

It's just not functioning. I'm not getting any errors but it just doesn't refresh the data.

Dim StartDate As Date, EndDate As Date
StartDate = Me.FilterDateStart
EndDate = Me.FilterDateEnd
Me.Filter = "[DateAssigned] >= #" & StartDate & "# AND [DateAssigned] <= #" & EndDate & "#;"
Me.FilterOn = True

The immediate window shows it picking up the correct dates from the variables.

Any other thoughts?

Fred

[Phred]

When I click in a DayAssigned field and go up to the strip and click selection --=> Between and enter the data range to filter it works perfect and filters the form. I would call that little Between box if I could. Or grab the code out of it.

[ssanfu]

When I click in a DayAssigned field and go up to the strip and click selection --=> Between and enter the data range to filter it works perfect and filters the form. I would call that little Between box if I could. Or grab the code out of it.

Do you know how to single step through the code? Set a break point on the "StartDate = Me.FilterDateStart" line.

On the form, set the dates, then click the CmdFilterMeals button. When the IDE appears, there should be a yellow line on the "StartDate = ....." line. Pressing the "F8" key will execute one line of code each keypress.

[Phred]

I'm familiar with single stepping in code. I can single step through it all the way and it finished without error. Still nothing refreshes.

Fred

[pbaldy]

Can you post the db here?

[Phred]

Sorry, it's to large. About 9 MB. This site restricts to 500 KB. I can't chop it down enough to get it even close.

Any other ideas.

[pbaldy]

How about exporting the relevant objects necessary for this function out to a new db, and zip that? All we really need is whatever is required to duplicate this problem.

[Phred]

I will see what I can do on this in the morning. Thanks. I'll take a useable backup and strip out everything else and see how low I can get the size. Thanks John.

[Phred]

Hi: I was thinking about establishing a SugarSync account (like box.com) where I can upload the entire database. I could then send you an invite to look at it. One of the Admins told me the upload size here was 2MB. I have tried uploading once before when I carved it down to 512KB and the system would not accept it. When I got it below that it wouldn't work. If your willing to look at it at SugarSync you will need to send me any email address you want to use, for me to send the invitation to. Even a temp email is fine. I will set up the account as I need it anyhow. If your not comfortable with this I will try carving it down in size but I'm not hopeful. Thanks, Fred.