Split For Filter Problem

[JustinS]

Creating an access database for work. Users will use a split form with only the datasheet visible to review and manipulate numeric data. On the form I have built in quick filters that consist of of textboxes in which the values are either raised or lowered with arrow buttons that have on-click events. I currently have the text boxes linked to the recordsource query criteria.



With all of this stated, the problem that I am having is that I need the the filter to act in the following manner:



textbox1=0 show all records.

textbox1>0 show all records >= textbox1.

textbox1<0 show all records <= textbox1.



I have considered trying to use multiple sql statements but I typically have about 3 of these quick filters on each form, and my project will eventually have about 20 forms. Additionally the three filters may be used in conjunction with each other. Therefore, That is a lot of sql statements to potentially mess up.



What ideas do you guys have to solve this problem? I really need help.

 

[theDBguy]

Hi. Is this a duplicate thread of this one?

Numeric Quick Filters in a Split Form

Creating an access database for work. Users will use a split form with only the datasheet visible to review and manipulate numeric data. On the form I have built in quick filters that consist of of textboxes in which the values are either raised or lowered with arrow buttons that have on-click...

www.access-programmers.co.uk

Can you post a sample db? It might be easier/faster to show you than to describe what you need to do.

 

[JustinS]

I can't really share because it is proprietary information. The problem is that I have my filter criteria textboxes linked to the query as criteria. That works fine when all the numbers are positive but the users in my company want the information presented in a way that would have negative percentages, positive percentages, and then at zero we would want to show all. I have tried the Me.Filter technique and I can't seem to get the filter to work with positive and negative numbers. Works great with zero. I have also tried the two attached methods, but neither of them seem to do anything.

I really would appreciate any help that can be offered.

 

[theDBguy]

I can't really share because it is proprietary information. The problem is that I have my filter criteria textboxes linked to the query as criteria. That works fine when all the numbers are positive but the users in my company want the information presented in a way that would have negative percentages, positive percentages, and then at zero we would want to show all. I have tried the Me.Filter technique and I can't seem to get the filter to work with positive and negative numbers. Works great with zero. I have also tried the two attached methods, but neither of them seem to do anything.

I really would appreciate any help that can be offered.

Hi. I'll review your attachments later to see I can make sense of them. It's really better to have a sample file to make things go quicker. We understand "sensitive" information, but wouldn't it be possible to still share the db without the sensitive data? For example, maybe you can replace all the sensitive information with "dummy" data. Just a thought...

 

[strive4peace]

hi Justin,

textbox1=0 show all records.
textbox1>0 show all records >= textbox1.
textbox1<0 show all records <= textbox1.

on the AfterUpdate Event of textbox1:

dim sFilter as string

with me.textbox1

   if nz(.value,0) = 0 then
      Me.Form.Filter = ""
      Me.FilterOn = False

   else
      if .value > 0
         sFilter  = "[Myfieldname] >= " & .value
      else
         sFilter  = "[Myfieldname] <= " & .value  'or maybe you want abs(.value)?
      end if

      Me.Form.Filter = sFilter
      Me.FilterOn = True
   end if

end with

where
Myfieldname is the name of the field to match

 

[JustinS]

@strive4peace will this work with numeric inputs as well? Also I have approximately three of these filter criteria textboxes on each of my forms. Will this method allow for filters to work in conjunction with each other? Thank you so much for the help.

 

[strive4peace]

hi Justin, this code is just to give you an idea, based on what you said. Please tell us more.

>"three of these filter criteria textboxes ... work in conjunction"

1. what are the names of the controls?
2. what are the names of the fields they filter on?
3. what is the data type for each field

thanks!

 

[Galaxiom]

Split forms not suited to doing anything advanced with code.
I highly recommend you abandon it and build your own split form using subforms.

 

[JustinS]

@strive4peace and @theDBguy

I tried the code that was posted previously and it didn't work. I think it has something to do with the code being directed toward a string and I'm trying to filter numbers. Below are the answers to the questions that were posted earlier.

1. what are the names of the controls? Capacity_Tol, STBY_Diff_Tol, TE_Tol
2. what are the names of the fields they filter on? Cap_Diff, STBY_Claim_Diff, TE_Diff
Cap_Tol controls Cap_Diff
STBY_Diff_Tol controls STBY_Claim_Diff
TE_Tol controls TE_Diff
3. what is the data type for each field? All fields are numbers and are formatted as percentages.

I know that it is odd to want to use negative percentages, but in this case it gives an idea as to whether or not the reported numbers are favorable or not. I really wish I could post a sample db, but everything is so intertwined that everything falls apart when I remove information that cannot be shared.

I really appreciate any and all help that has been offered.

 

[JustinS]

I think another problem is that the fields that I want to filter on are calculated in a query. They are not part of any table. Could this be causing the issue?

 

[theDBguy]

I think another problem is that the fields that I want to filter on are calculated in a query. They are not part of any table. Could this be causing the issue?

I doubt that, but can't really tell without seeing it.

 

[JustinS]

@theDBguy and @strive4peace here is a dummy version of what I am using. The form in question is "O". Some features of the form will not work because I have deleted them. Please let me know if you have questions. I really appreciate your help.

 

[theDBguy]

@theDBguy and @strive4peace here is a dummy version of what I am using. The form in question is "O". Some features of the form will not work because I have deleted them. Please let me know if you have questions. I really appreciate your help.

Hi Justin. Not sure if this is what you want, but this is probably how I would do it.

Please note, I only did the Capacity filter.

Hope it helps...

 

[JustinS]

This looks great. The only problem that I see is that when I hit the reset default button it doesn't requery, and when I hit enter is the textbox value goes to 1. Any ideas?

You are the best. I cannot thank you enough.

 

[theDBguy]

This looks great. The only problem that I see is that when I hit the reset default button it doesn't requery, and when I hit enter is the textbox value goes to 1. Any ideas?

You are the best. I cannot thank you enough.

Hi. Like I said earlier, I only played with the Capacity area. Here's I added the Reset button. Sorry, I don't understand what you mean by hitting enter? Where are you hitting it?

 

[JustinS]

It works awesome now. I have been trying to figure this out for days and you did it in minutes. Thanks a bunch.

 

[JustinS]

By the way, what did you do to resolve the reset issue?

 

[theDBguy]

It works awesome now. I have been trying to figure this out for days and you did it in minutes. Thanks a bunch.

Hi. You're welcome, but I couldn't have done it without your help. Trying to figure out a solution from word description is sometimes hard. I'm glad to you made the effort to create a demo version for us, and it paid off. Also, I don't know what approach you were using earlier, but that's why I asked if you were using the Form's Filter Property, because I think it's a simpler approach. Cheers!

 

[theDBguy]

By the way, what did you do to resolve the reset issue?

I just added the same code I used for the Capacity buttons.

 

[JustinS]

I hate to continue bothering you, but I cant seem to get the other filters to work. I just extended your select case and it they don't work. Is there something that I am missing. I really want to understand how this done so that I can apply it my real database. I'm attaching the changes that I have made.

I appreciate your patience and help.