473,394 Members | 4,034 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

MS Access Form Filter

4
Hi,

I've developed a form filter that produces a number of hours budgeted by employee/location/practice group/experience level and date available using some Allen Browne code. I have two command buttons, cmdFilter and cmdFilterAvail. cmdFilter filters the form based on the criteria selected via my list and text box filters. One of the fields in the form, text51 has a control source:=IIf([clientname]="Unassigned Time",[BudgetedHours],Null)

On click, cmdFilter filters my results but also shows the null values present in the data set where clientname <> "Unassigned Time". I'd like cmdFilterAvail to be able to keep the filters applied, but then narrow my data set to NON-NULL text51 values thereby showing only employees with available time.

Code for frmFilterAvail is identical to frmFilter and needs to have the NON-NULL piece added and I don't know how to do it...Help would be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdfilteravail_Click()
  2.   'Purpose:   Build up the criteria string from the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.     Dim strWhere As String                  'The criteria string.
  10.     Dim strExperienceLevel As String
  11.     Dim strPracticeGroup As String
  12.     Dim strLocation As String
  13.     Dim varSelected As Variant
  14.     Dim lngLen As Long                      'Length of the criteria string to append to.
  15.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  16.  
  17.     '***********************************************************************
  18.     'Look at each search box, and build up the criteria string from the non-blank ones.
  19.     '***********************************************************************
  20.     'Text field example. Use quotes around the value in the string.
  21.     If Me.txtFilterExperienceLevel.ItemsSelected.Count > 0 Then
  22.     For Each varSelected In Me.txtFilterExperienceLevel.ItemsSelected
  23.     strExperienceLevel = strExperienceLevel & ",""" & Me.txtFilterExperienceLevel.ItemData(varSelected) & Me.txtFilterExperienceLevel & """"
  24.     Next varSelected
  25.     strExperienceLevel = Mid(strExperienceLevel, 2)  'REMOVES LEADING COMMA
  26.     strWhere = strWhere & "([ExperienceLevel] IN (" & strExperienceLevel & ")) AND "
  27.     End If
  28.  
  29.     If Not IsNull(Me.txtFilterExperienceLevel) Then
  30.         strWhere = strWhere & "([ExperienceLevel] = """ & Me.txtFilterExperienceLevel & """) AND "
  31.     End If
  32.  
  33.     If Me.txtFilterPracticeGroup.ItemsSelected.Count > 0 Then
  34.     For Each varSelected In Me.txtFilterPracticeGroup.ItemsSelected
  35.     strPracticeGroup = strPracticeGroup & ",""" & Me.txtFilterPracticeGroup.ItemData(varSelected) & Me.txtFilterPracticeGroup & """"
  36.     Next varSelected
  37.     strPracticeGroup = Mid(strPracticeGroup, 2)  'Removes leading comma
  38.     strWhere = strWhere & "([PracticeGroup] IN (" & strPracticeGroup & ")) AND "
  39.     End If
  40.  
  41.     If Not IsNull(Me.txtFilterPracticeGroup) Then
  42.         strWhere = strWhere & "([PracticeGroup] = """ & Me.txtFilterPracticeGroup & """) AND "
  43.     End If
  44.  
  45.     If Me.txtfilterlocation.ItemsSelected.Count > 0 Then
  46.     For Each varSelected In Me.txtfilterlocation.ItemsSelected
  47.     strLocation = strLocation & ",""" & Me.txtfilterlocation.ItemData(varSelected) & Me.txtfilterlocation & """"
  48.     Next varSelected
  49.     strLocation = Mid(strLocation, 2)  'Removes leading comma
  50.     strWhere = strWhere & "([Location] IN (" & strLocation & ")) AND "
  51.     End If
  52.  
  53.     If Not IsNull(Me.txtfilterlocation) Then
  54.         strWhere = strWhere & "([Location] = """ & Me.txtfilterlocation & """) AND "
  55.     End If
  56.  
  57.     'Another text field example. Use Like to find anywhere in the field.
  58.     If Not IsNull(Me.txtFilterMainName) Then
  59.         strWhere = strWhere & "([EmployeeName] Like ""*" & Me.txtFilterMainName & "*"") AND "
  60.     End If
  61.  
  62.     'Date field example. Use the format string to add the # delimiters and get the right international format.
  63.     If Not IsNull(Me.txtStartDate) Then
  64.         strWhere = strWhere & "([BudgetDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
  65.     End If
  66.  
  67.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  68.     If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
  69.         strWhere = strWhere & "([BudgetDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  70.     End If
  71.  
  72.     '***********************************************************************
  73.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  74.     '***********************************************************************
  75.     'See if the string has more than 5 characters (a trailing " AND ") to remove.
  76.     lngLen = Len(strWhere) - 5
  77.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  78.         MsgBox "No criteria", vbInformation, "Nothing to do."
  79.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  80.         strWhere = Left$(strWhere, lngLen)
  81.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  82.         'Debug.Print strWhere
  83.  
  84.        'Finally, apply the string as the form's Filter.
  85.         Me.Filter = strWhere
  86.         Me.FilterOn = True
  87.     End If
  88. End Sub
Sep 8 '10 #1
0 1406

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Mike O. | last post by:
MS Access 2003 "filter by form" has drop down lists that allow the user to select values for each field to filter by. However, once some values are selected,the remaining dropdown lists remain the...
14
by: Nothing | last post by:
I have a form that has a Tab Control on it. On one of the tab control pages, there are only 3 pages, is a combo box based on a field called departments in a table. When the user selects one of...
4
by: wecka | last post by:
Hello All, Does any one know how to use the form filter in Access to provide partial match? When you choose to "form filter," all text boxes will convert to combo boxes where you can choose from...
2
by: Henrootje | last post by:
Hello there! I have a piece of code in a module (function) that constructs a filter. But now I run into a problem..... It seems that I can only set a filter form a module if I open the form...
5
by: phill86 | last post by:
Hi, I have a form that I have applied a filter to by using combo boxes which works fine. Is there a way to apply that filter to the forms underlying query Here is the code that I use to...
2
by: phill86 | last post by:
Hi, I am filtering a report using the form filter with the following code DoCmd.OpenReport "report1", acViewReport, , Me.filter Which works fine untill I filter the form on one of the...
1
by: whkoh | last post by:
I have two tables: Customer and Project with primary keys Customer ID and Project ID respectively. A third table (Involvement) is the intersection table storing Customer ID and Project ID as...
1
by: Obenash | last post by:
Is it possible to filter a data entry form with multiple subforms. I have a form that is used to enter data into the database and the form has multiple subforms and I wanted to filter it to make it...
3
by: jsneed | last post by:
I have a database that is going pretty good. The only problem I have been having is using a form to filter a table by description. In the table I have Part Number, description, vendor, utilized on,...
7
by: munkee | last post by:
Hi all, I am using Allen's excellent form filter script to filter the results from a query. I would now like to add some further functionality. How do I go about displaying say the top N costs...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.