Filter a field on a form with value entered in a text field

[Tskutnik]

Easy one - but I'm stuck.

I have a form with a bunch of fields and the embedded Access filter function will not work well for the user, so I need some text boxes for the user to enter variables which will filter the form.

To use on field as the example:
1) The unbound text box where the user will enter a numeric variable = "PriceDietzUpFilter"
2) The field on the form that I want the "PriceDietzUpFilter" to filter = "PriceVsDietz"
3) Both of the fields above are on the same form, named "MasterRecon"

If blank I want the filter off, of course.

Here is the mess I have now, in the After Update event. It errors on the line noted below.

Private Sub PriceDietzUpFilter_AfterUpdate()
If Len(Me.PriceDietzUpFilter) not 0 Then
    Me.FilterOn = True
    Me.Filter = "PriceVsDietz '" & Me.PriceDietzUpFilter & "'" (ERROR)
Else
    Me.FilterOn = False
    Me.Filter = ""
End If

I will eventually apply this filter logic to a number of fields on the form, but if I can get this one to work I can figure out the others. If there are any differences between filtering numbers or text please let me know.

I'm not a programmer (as you can see) so if you could be so kind to give the entire block of code that would be great.

 

[AccessMSSQL]

What's the error you are getting, please?

 

[pbaldy]

If the data type of the field is numeric it would be:

Me.Filter = "PriceVsDietz " & Me.PriceDietzUpFilter

You also want to reverse the Filter and FilterOn lines.

 

[AccessMSSQL]

Also, you may want to make sure a filter argument already exists for the form. Try manually inputting a filter in the form filter property and saving it, then try your operation again. For instance, just put 1=1. I think I've had issues with using that method before if a filter didn't already exist on the form.

 

[Tskutnik]

Pbaldy: I made the changes and now get en error on the Me.Filter = true line
AccessMSSQL: the error: Run-Time 3075 Syntax error Missing Operator) in query expression "PriceVsDietz"4".
Here is the current code

If Len(Me.PriceDietzUpFilter) >0 Then
    Me.FilterOn = True
    Me.Filter = "PriceVsDietz " & Me.PriceDietzUpFilter
Else
    Me.Filter = ""
    Me.FilterOn = False
End If

 

[pbaldy]

You didn't reverse the order of them.

 

[pbaldy]

Oh, and you left out the "="

Me.Filter = "PriceVsDietz = " & Me.PriceDietzUpFilter

 

[Tskutnik]

AccessMSQL: I think I tried you suggestion correctly and got an interesting result...
I used the Access filter on the PriceVsDietz field and in the Number Filter option typed "1". Up popped the error "Enter a valid value"

 

[AccessMSSQL]

hmmmm...not sure if you did the right thing. You want to modify the actual Form filter, not the pricevsdietz field filter. Not sure what filter you are referring to Number Filter? And you should put in "1=1" in the filter of the form. See screenshot.

 

[Tskutnik]

Accessmsql: I did as you suggested and when the form was opened and I try to enter a value in the filter field again I get the message: "The expression after update you entered as the event property setting produced the following error: Ambiguous name detected: Pricedietzupfilter_AfterUpdate." With 2 addiitonal bulltes of explanation.

 

[Tskutnik]

pbaldy: Sorry, i reversed the wrong ones.
Here is the current code. Now I get "Division by zero" error in the step noted below

Private Sub PriceDietzUpFilter_AfterUpdate()
If Len(Me.PriceDietzUpFilter) > 0 Then
    Me.Filter = "PriceVsDietz = " & Me.PriceDietzUpFilter
    Me.FilterOn = True (ERROR)
Else
    Me.Filter = ""
    Me.FilterOn = False
End If
End Sub

 

[AccessMSSQL]

Can you post your project?

 

[Tskutnik]

Unfortunately no. Far too much sensitive data to strip out.

 

[AccessMSSQL]

PriceVsDietz is numeric or text? I'll post an example.

 

[AccessMSSQL]

what version of Access?

 

[AccessMSSQL]

I created a simple form that mimicks your filtering and it works fine. Do you have other code in your form that is running that may be causing the error? Check what is running on the OnCurrent event of the form?

Attached is my test db (access 2010). I have a text field loaded with Value1, Value2, Value3, and a numeric field loaded with a few numbers - you can test both numeric and text filters - it works fine.