Filter a form using Option Group

[tony_1974]

Hi Everyone,

I am a newbie in this forum. I am currently working on a access db that has multiple forms/Queries/Tables.
The form that I am working on, comes from a Query. The information in the form is re filtered based on date range. Then, the remaining results need to be filtered based on a specific value from one of the fields [Quantity]. I added a group option to the form, so it can show the specific values that I am looking form based on date/range and Quantity. However, it's not working properly, so I am missing something here.

Can anyone take a look at this and provide some help? Much appreciated:

Here is the code for the date range:

Private Sub cmdsearch_Click()
' Search button
Call search
End Sub

Sub search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDatefrom) Or IsNull(Me.Orderdateto) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.OrderDatefrom.SetFocus
Else
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") order by [Ship date]"
DoCmd.ApplyFilter task

End If

End Sub

And here is the VBA code for the option Group:

Private Sub Frame1_Click()
Dim strFilter As Integer

Select Case Frame1


Case 1
strFilter = "[Quantity] < = '50000'"
Forms!frmInTransit.Form.Filter = strFilter
Forms!frmInTransit.Form.FilterOn = True

Case 2
strFilter = "[Quantity] > = '50000'"
Forms!frmInTransit.Form.Filter = strFilter
Forms!frmInTransit.Form.FilterOn = True

End Select
End Sub

 

[sneuberg]

I think you want to combine these filters. The way you have it one will overwrite the other. I've attached a database that demonstrates how the values of multiple controls (in this case text boxes) are combined together to form a single filter. I think you should be able to adapt this to your situation.

 

[tony_1974]

Thank you Sneuberg,

Perhaps I didn't explain myself correctly. If so, my apologies

What I am trying to is: After the form has been filtered based on date range. I want to be able to apply the Option Group, to select based on additional criteria (A or B), so that the form can show the results based on:

1 the date range And
2 the Option Group selected

I hope I clarified my point further

Thanks,

Anthony

 

[sneuberg]

With the method used in the database I posted you can filter on the date range, the Option Group, both or neither at any time.

 

[tony_1974]

Ok,

I give it a shot, but now I am getting this error

Run Time Error 3075:
Syntax Error (missing operator) in query expression .....

My code is listed below......

Private Sub Frame1_Click()
Dim strCriteria, task As String

If Frame1 = 1 Then
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") where [Quantity_KG_]>='50000' &"
DoCmd.ApplyFilter task

ElseIf Frame1 = 2 Then
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") where [Quantity_KG_]<='50000' &"
DoCmd.ApplyFilter task

End If
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"

End Sub


Any ideas what's missing here?

Thanks,

Anthony

 

[BigHappyDaddy]

First, I am guessing you current problem resides in the string task. You have a '&' trailing the where statement. Then you are using task as a filter. Try removing that character.

Second, i personally belive your original method was a closer solution. But instead of applying task as a filter, set it as the form's recordsource. Then your filter date code should work as expected.

 

[tony_1974]

Hi BigHappyDaddy,

Thanks for the response,

This is what I wrote based on your first suggestion:

If Frame1 = 1 Then
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") where [Quantity_KG_]>='50000'"
DoCmd.ApplyFilter task

ElseIf Frame1 = 2 Then
strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") where [Quantity_KG_]<='50000'"
DoCmd.ApplyFilter task

I am getting a run time error 3075 (Syntax Missing Operator)....in Query Expression...

Question regarding your Second Comment:
How would I apply the option Group as Form RecordSource, I am a little novice here when it comes to access VBA.

Thanks again!

Anthony

 

[BigHappyDaddy]

Ok, for your error. You have 2 where statements in you variable task. Change the second where to and.

As for the recordsource, just as you have it except do not include the and statement. Replace docmd.applyfilter with

me.recordsource=task

Then readd docmd.applyfilter, but instead of task, use "[quantity_kg]>='50000'"

Make the same changes in the else section.

We can also tighten up your code, but that can wait until you get this working.

 

[tony_1974]

Hi,

Sorry for my late reply,

I made the recommended changes, but it seems that I am missing something here.

I substitute the where for and and it looks like these:

strCriteria = "([Ship date] >= #" & Me.OrderDatefrom & " # And [Ship date]<= #" & Me.Orderdateto & "#)"
task = "Select * From QryIntransit where (" & strCriteria & ") And [Quantity_KG_]>='50000'"
Me.RecordSource = task

I also add the additional Line:

DoCmd.ApplyFilter "[Quantity_KG_]>='50000'"

However, when I run the code on the form, I got a pop up box, asking me to enter the parameter value. I am getting closer, but something else is missing

Any ideas?

Thanks,

Anthony:banghead:

 

[BigHappyDaddy]

What happens if you remove the docmd.applyfilter line?

Sent from my SM-T530NU using Tapatalk

 

[tony_1974]

Hi,

I removed the filter as indicated and whenever I click one of the options, I get a pop message that says:

Enter Parameter value for the Quantity_KG

Any ideas?