Filtering results in a subform from main form - between dates!

[OllyJ]

Hope you can help guys.

I have a scheduling database, the main schedule table contains the following fields:

AddedDate
Day/Night
Machine
Arm/Head
StockCode
ToolNumber
Operator
etc

I then have a continuous subform based on this table. This subform sits within the detail section of my main form titled 'frm_schedule'.

Because there are many records for each variable in the table (Can be up to 100 / day) I need to set up multiple filter combo boxes in the header of my main form. I want to set them up so that they filter the records down 'after update'.

The first i am struggling with is 'this week', here is the code I use and I keep getting syntax errors etc...

Private Sub DatePick_AfterUpdate()
 
If Me.DatePickCombo = "This Week" Then
 
Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] = Between #" & DateAdd("d",1-Weekday(Date(),2),Date()) & "# And #" & DateAdd("d",7-Weekday(Date(),2),Date()) & "#"
 
Me.Frm_Schedule_Subform.Form.FilterOn = True
 
Me.Form.Refresh
 
End If

1. Do you know what's going wrong with my code

2. In using this method I need to be able to switch individual filters off, is there an easy way of naming them so that it makes code easier the more limiters I introduce to the header of my form?

Many thanks in advance, OllyJ

[MikeTheBike]

Hope you can help guys.

I have a scheduling database, the main schedule table contains the following fields:

AddedDate
Day/Night
Machine
Arm/Head
StockCode
ToolNumber
Operator
etc

I then have a continuous subform based on this table. This subform sits within the detail section of my main form titled 'frm_schedule'.

Because there are many records for each variable in the table (Can be up to 100 / day) I need to set up multiple filter combo boxes in the header of my main form. I want to set them up so that they filter the records down 'after update'.

The first i am struggling with is 'this week', here is the code I use and I keep getting syntax errors etc...

Private Sub DatePick_AfterUpdate()
 
If Me.DatePickCombo = "This Week" Then
 
Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] = Between #" & DateAdd("d",1-Weekday(Date(),2),Date()) & "# And #" & DateAdd("d",7-Weekday(Date(),2),Date()) & "#"
 
Me.Frm_Schedule_Subform.Form.FilterOn = True
 
Me.Form.Refresh
 
End If

1. Do you know what's going wrong with my code

2. In using this method I need to be able to switch individual filters off, is there an easy way of naming them so that it makes code easier the more limiters I introduce to the header of my form?

Many thanks in advance, OllyJ

Hi

Very quickly of the top of my head try this:

Private Sub DatePick_AfterUpdate()
 
If Me.DatePickCombo = "This Week" Then
 
    Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] Between #" & Foprmat(DateAdd("d", 1 - Weekday(Date, 2), Date), "mm/dd/yy") & "# And #" & Format(DateAdd("d", 7 - Weekday(Date, 2), Date), "mm/dd/yy") & "#"
 
    Me.Frm_Schedule_Subform.Form.FilterOn = True
 
Me.Form.Refresh
 
End If

I have removed the '=' from before the Between and added the format function, but not sure if this is neccesarry for you, but would be for me in the UK.

MTB

[OllyJ]

Thanks Mike, tried your way but it wouldn't filter at all?

Im from UK too so i do need it that format but have already formatted the date box within the form thanks.

OllyJ

[MikeTheBike]

Thanks Mike, tried your way but it wouldn't filter at all?

Im from UK too so i do need it that format but have already formatted the date box within the form thanks.

OllyJ

Hi

A few questions.

When you say you tried my way, did you remove the '=' AND use the format function to produce US dates (NOT UK dates) ??

When you say it woudn't filter at all, do you mean it doese not now give syntax errors, but just does not return any records ?

It is not the date box on the form that needs formatting!!

On the basis that you are not now getting syntax error, may I suggest adding a msgbox as below, and see what you get!

Msgbox "[AddedDate] Between #" & Format(DateAdd("d", 1 - Weekday(Date, 2), Date), "mm/dd/yy") & "# And #" & Format(DateAdd("d", 7 - Weekday(Date, 2), Date), "mm/dd/yy") & "#"
 
Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] Between #" & Format(DateAdd("d", 1 - Weekday(Date, 2), Date), "mm/dd/yy") & "# And #" & Format(DateAdd("d", 7 - Weekday(Date, 2), Date), "mm/dd/yy") & "#"
 
Me.Frm_Schedule_Subform.Form.FilterOn = True
 

The msgbox should display the dates in US format, ie. mm/dd/yy (not dd/mm/yy).

Let us now what happens.


MTB

[OllyJ]

MTB

I've sorted it!! Many apologies for the misunderstandings. When I went through everything again from the start it worked and limited the results to 'this week'.

Here is the code I used for anyone else who might need it:

Private Sub DatePick_AfterUpdate() 
 
If Me.DatePick = "This Week" Then     Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] Between #" & DateAdd("d", 1 - Weekday(Date, 2), Date) & "# And #" & DateAdd("d", 7 - Weekday(Date, 2), Date) & "#"     
 
Me.Frm_Schedule_Subform.Form.FilterOn = True 
 
Me.Form.Refresh 
 
End If
 
End Sub 

There are still issues that i'm strggling with however Mike, if you or someone you know could help in any way that would be great.

My main problem with this form is that I need several filters to be running in sync' - i.e. 'This week' to be selected and then 'Days'. But the results need to show results from days, this week. When I run multiple filters it replaces the last filter with the latest one to be true?

- Is there a way of applying multiple filters based on what is selected in each combobox?

- Is there a way of naming filters so as to easily reference them within a chunk of code?

Hope you can help

OllyJ

[MikeTheBike]

MTB

I've sorted it!! Many apologies for the misunderstandings. When I went through everything again from the start it worked and limited the results to 'this week'.

Here is the code I used for anyone else who might need it:

Private Sub DatePick_AfterUpdate() 
 
If Me.DatePick = "This Week" Then     Me.Frm_Schedule_Subform.Form.Filter = "[AddedDate] Between #" & DateAdd("d", 1 - Weekday(Date, 2), Date) & "# And #" & DateAdd("d", 7 - Weekday(Date, 2), Date) & "#"     
 
Me.Frm_Schedule_Subform.Form.FilterOn = True 
 
Me.Form.Refresh 
 
End If
 
End Sub 

There are still issues that i'm strggling with however Mike, if you or someone you know could help in any way that would be great.

My main problem with this form is that I need several filters to be running in sync' - i.e. 'This week' to be selected and then 'Days'. But the results need to show results from days, this week. When I run multiple filters it replaces the last filter with the latest one to be true?

- Is there a way of applying multiple filters based on what is selected in each combobox?

- Is there a way of naming filters so as to easily reference them within a chunk of code?

Hope you can help

OllyJ

Hi

Please you have got it working. However, if your computer is set to UK dates, I still believe you need to use the format statement I suggested (at least I have never found it works properly without) unless you have some setting I don't know about!?

The reason it works at present, I believe, is because the two date are both in January, but after the 12th. These dates (Day & Month) cannot be interpreted as any othe date. However, if the date was say 8th Jan then Access (VBA) would interpret this as 1st of August, unless you tell it otherwise. Try it and if you find any different, then please let us know.


With regard to multiple filters, well yes you can, but it involes some work.

Two ways; both use buttons to apply and reset filter(s)

1) Wite code to check the filer controls and constuct the filter as before in the button Click event; also the reset.

2) Use a stored query for the Form Record Source and connect it to the filter controls in the query designer. This method is a little move tricky as you will, no doubt, need to use the Like and the * wild card characture to cater for the Null case(s). Also you will need to devise a strategy to cater for no date range specified, if that is required (I normally use the Max & Min dates form the date field being filterd in that case - needs coding and hidden controls in the form!). This method does not stickly need buttons as you can requery the form after each filter setting, but I find continually requerying the form when setting multiple filters vaguely annoying.


HTH

MTB

[OllyJ]

Thanks for your input MTB

I have now got the date filters working by doing it through the query as a record source for the continuous subform like you said.

I am now trying to restrict the Day / Night by a textbox in the header and as you wisely spotted in advance, i need something to enter in order for there not to be a limit and both results for day and night shown...is there something that can be entered in the textbox (which is effectively the querys criteria) so as to not restrict the results?

Hope you can help again, OllyJ