Urgent - Filter Sub Form in VB by button Filter Property

[pbaldy]

Why not just email it to me:

removed to prevent spam

[pbaldy]

Your basic problem is that the data is in a subform, not the form the code is in. This works:

Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt er = "MealName = 'corn dogs - adult'"
Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt erOn = True

[Phred]

I continue to have problems with filtering. As I continue to read I think it is something basic I am missing as I have never used filters before.

I see "Filter" in the property page for Tables, forms, queries, and VB coupled with several different kinds of methodology. But no information on the method that is best for my situation.

It seems I need to create a filter and save it somewhere and then I can toggle it on and off.

My code seems to be right. Here are my basic questions:

Do I need to create and save a filter first? If so, where? and by what method?

Do I then activate the filter and deactivate the filter from VBA? Or is everything created within the code in VBA? See above.

I want to split and compile the database. Does this impact where and how I create the filter?

Can anyone see what I am missing?

I can make a filter run from the ribbon and have it filter and unfilter so I know it will work.

I have read every article everyone has suggested so far and any I can find on my own as well as my books.

I have a delivery date of this comming Monday so I am feeling pressure. This is the last item on my to do list.

Thanks all for bearing with me on this. Fred

[Phred]

I deleted all of my code under my filter button. I pasted back in only PBaldy's code as above.

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

I returned to the form and entered the dates 3/7/2012 and 3/9/2012.

I clicked the button and something actually happened. The data form filtered but it did not filter by the dates I put on the form. It filtered retaining 3/7/2012 - 3/15/2012



I put a watch on the variables and the dates are being captured right. I noticed it is returning 1/2 of the records in the table which consists of March 2012.

1. This is the first time it worked at all. I don't know why it worked.

2. Could it be returning a previous filter date range?

3. After filtering the Togle button on the ribbon activates. If I click it I get an error "Syntax Error in Query Incomplete Query Clause."

4. In the Filter Off button I created On Click:

me.filteron=false

It doesn't do anything. What would be the code to restore this to the unfiltered state?

Thanks Fred

[pbaldy]

The code I posted above (disregarding the spaces added by the forum) worked fine, so your task should simply be one of building a valid filter string. You haven't said what your "problems" are now, so not sure how to point you. In answer to your questions, no you don't have to create a filter first. In a split db the code would be in the front end, so no effect on how you create it.

The sample db in this FAQ demonstrates a somewhat similar method, but it sets the row source of the subform rather than the filter. Frankly either should work fine.

http://www.baldyweb.com/BuildSQL.htm

[pbaldy]

I don't know if you're working with something different than you sent me. This works:

Dim StartDate As Date, EndDate As Date
StartDate = Me.FilterStartDate
EndDate = Me.FilterEndDate
Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt er = "[DayAssigned] >= #" & StartDate & "# AND [DayAssigned] <= #" & EndDate & "#"
Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt erOn = True

Noting that your form control names are different, the data field name is different, and you still haven't referred to the subform.

[Phred]

John I seem to be having some real problems with navigating the forum. I just found your response from yesterday. Thanks so much for responding. I think you are right.

I made an error in this posting so I have edited the info.

My DayAssigned is coming up as a time and my StartFilterDate and EndFilterDate are comming up as short dates. So far everywhere I look DayAssigned is set as a short date. But as a result I get this error:



I am going through everything to make sure the DayAssigned is a short date but I'm not finding a problem so far. Is there a way for force it to display date instead of time in the code? I have read the articles on dates but don't see anything that I understand since it is already in a short date format.

Is it possible that the [DayAssigned] in the expression is being read as time and needs [#" DayAssigned #"] or some variation on that, that forces it to be seen as a date? I have gone through everything I can find an it is set as a short date.

Thanks Fred

[pbaldy]

I'll reply in case "John" isn't around.

It appears that the dates aren't coming through from the textboxes (0 could be represented like that). I don't think you want the semi-colon either. Did you try what I posted? It worked in the db you sent me. You're saying DayAssigned in your text but the error has DateAssigned.

[Phred]

Yes, I implemented your code immediately. I deleted everything in the module and pasted your code in verbatim. I noticed the discrepancy between the DateAssigned and DayAssigned and corrected for it. I still got the time error so I eliminated the semi-colon and the "time" error went away. I get no error messages now. The filter runs without any error but it now filters out all the records.

I added records for the first week of April and set the filter dates to show between 4/1/2012-4/7/2012, clicked to filter and all records vanished from the screen. If I click my remove filter button all the records come back. DoCmd.RunCommand (acCmdRemoveFilterSort)

So I don't think it is picking up the filter dates either. Here is the code as it sits right now:
Dim StartDate As Date, EndDate As Date

Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt er = "[DayAssigned] >= #" & StartDate & "# AND [DayAssigned] <= #" & EndDate & "#"

Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt erOn = True

You don't give your name but thanks for your help. I'm going to see if I can set up watches on the variables and confirm what is comming through.

Fred
__________________________________________

I set

StartDate=StartDate

EndDate=EndDate

I set watches on both and they each showed up in the Watch as time 12:00 and not dates. So the same problem exists I am getting the time portion of the date field only even though they are set as ShortDate.

[pbaldy]

My name (Paul) is in my sig, which unfortunately the forum drops from some posts. Not sure what to say, as that code works fine for me. See attached.

[Phred]

Hi Paul:

Thanks for your efforts. I placed your code in my chopped down version I sent you and had the same problem. I moved the chopped down version to another machine and still had the same problem. All the records are filtered out. So I'm stumped.

Could we pull it apart with DatePart in vb and see if we could reassemble it with the date or is that just wishfull thinking. Is there any way to do this with queries? The subform is based on a query and I can't put another criteria in it. Is there anyone else you can think of that might have some insight?

Thanks so much.

Fred

[Phred]

To all the split personalities of John Gault:

I added two additional variables not influenced whatsoever by the bad binary karma that is haunting me. This works perfect:

Dim dtstart As Date, dtend As Date

dtstart = Me.StartDate

dtend = Me.EndDate

Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt er = "[DayAssigned] >= #" & dtstart & "# AND [DayAssigned] <= #" & dtend & "#"

Me.Sub_Frm_ScheduledMealsByCategoryChild.Form.Filt erOn = True

Phred the Phabulous

[Phred]

Dear Syble: Honestly, it was all you guys/gals inside John Gault that put me on the right track.

[pbaldy]

Ah, that's where you got the "John" from. That's a book character. Glad you got it working!