Split For Filter Problem (1 Viewer)

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
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

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
Hi. Is this a duplicate thread of this one?


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

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
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.
 

Attachments

  • Array.PNG
    Array.PNG
    26.7 KB · Views: 169
  • criteria.PNG
    criteria.PNG
    14.9 KB · Views: 164
  • My array.PNG
    My array.PNG
    22.9 KB · Views: 156
  • Query Criteria.pdf
    25 KB · Views: 166
  • Expression.pdf
    25.1 KB · Views: 155
  • Oil.pdf
    37.2 KB · Views: 169
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
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

AWF VIP
Local time
Today, 11:02
Joined
Apr 3, 2020
Messages
1,003
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:

Code:
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

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
@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

AWF VIP
Local time
Today, 11:02
Joined
Apr 3, 2020
Messages
1,003
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

Super Moderator
Staff member
Local time
Tomorrow, 02:02
Joined
Jan 20, 2009
Messages
12,850
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

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
@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

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
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’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
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

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
@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.
 

Attachments

  • test4.zip
    205.8 KB · Views: 169

theDBguy

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
@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...
 

Attachments

  • test4.zip
    140.3 KB · Views: 178

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
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

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
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?
 

Attachments

  • test4.zip
    166.4 KB · Views: 167

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
It works awesome now. I have been trying to figure this out for days and you did it in minutes. Thanks a bunch.
 

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
By the way, what did you do to resolve the reset issue?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,438
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!
 

JustinS

Member
Local time
Today, 12:02
Joined
Apr 11, 2020
Messages
58
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.
 

Attachments

  • test5.zip
    144.9 KB · Views: 161

Users who are viewing this thread

Top Bottom