Been a while since I needed to post on this forum, but I found something rather disturbing today; either that or I have missed something obvious. I have a large query of financial data, and I filter out certain codes which I don't want to include. The filter is;
<>"BL01" And <>"SS01"
Which works as it should, filtering out all BL01 and SS01. However, it also filters out any NULL values, which is something I would not expect it to do. When I enter NULL as a filter, I get the records I expect. What am I doing wrong here? I am using Access 2003 SP3 on Windows XP Service Pack 2
Can't figure out the syntax for specifying a null value for a text or combo box when filtering a table via a form. "Is Null" seems to work for query criteria but doesn't work for form controls. If one right-clicks on a form control displaying a null value, one of the choices is "Equals Blank" which if selected works fine but what does one use for the text filter if the record for the control in question does is not displaying a null value?
Both have the same source list but I would like the ref 1 list to exclude ref 2 and the ref 2 list to exclude ref 1 but I am struggling when either value is Null gives me no list.
Ref 1 row source
SELECT [Reference Probes].Ref, IIf([Forms]![frmCalibration]![Ref 2]=Null,([Reference Probes].[Ref]) Is Not Null,[Reference Probes].[Ref]) AS Expr1 FROM [Reference Probes] WHERE (((IIf([Forms]![frmCalibration]![Ref 2]=Null,([Reference Probes].[Ref]) Is Not Null,[Reference Probes].[Ref]))<>[Forms]![frmCalibration]![Ref 2]));
Ref 2 row source
SELECT [Reference Probes].Ref FROM [Reference Probes] WHERE ((IIf([Forms]![frmCalibration]![Ref 1]=Null,([Reference Probes].Ref) Is Not Null,[Reference Probes].[Ref])<>[Forms]![frmCalibration]![Ref 1]));
Ref 1 after update has Me.Ref_2.Requery Ref 2 after update has Me.Ref_1.Requery
As you can see I have tried two different ways but both don't work.
I have a main form with multiple combo boxes that filter a subform datasheet. In my main form combo box [PR_Filter] I added a selection titled "<Blanks>". When I select "<Blanks>", I want it to filter my subform field [PR] for NULL values. If I select anything else I want it to filter on that selection. I cannot get the NULL filter to work.
Here is the code that I have (Red is the field I need the NULL values):
Private Function PurchaseFilter() Dim strFilter As String Dim bFilter As Boolean bFilter = False strFilter = ""
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check - transfer - taxes - cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR Else Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'" Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'" End If Me.FilterOn = True
I m trying to make form which filters my records and generates a report..here's where I am
Code: Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.
I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.
Code: SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter FROM qryReportSelector2_Authority WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
I am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]
This pulls up the current record fine. for the form and flags the folloing in the property filter sectin of the form
(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))
PART 2
Now I am trying to use microsofts how to filter a report using a forms filter... This picks up on the Invoice query as shown above but does not just insert the query results...
Is there better code or another way to approach this... Currently i am using:
Name:cmdOpenReport Caption: Open Report OnClick: [Event Procedure]
Private Sub CmdOpenReport_Click() If Me.Filter = "" Then MsgBox "Open an Invoice First" Else DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter End If End Sub
Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
I have a listbox on a form displaying records which can be filtered via a number of comboboxes. These comboboxes allow the user to select "<ALL>" or a specific value from the list. It all works perfectly.
However, I would like to be able to select a filter value of "Null" on one of the comboboxes which would display those records without a value in the relevant field. How can I do this?
The following (simplified) code is what I'm using:
SQL for the results listbox: SELECT qryClientSearch.ID, qryClientSearch.Name, qryClientSearch.AccountManager, qryClientSearch.AccountMgr FROM qryClientSearch WHERE ( ((qryClientSearch.ID) Like fCboSearch([Forms]![frmClientSearch]![cboSearchName])) AND ((qryClientSearch.AccountManager) Like fCboSearch([Forms]![frmClientSearch]![cboSearchAccountMgr]) Or (qryClientSearch.AccountManager) Is Null) ) ORDER BY qryClientSearch.Name;
SQL code for the combobox filter: SELECT qryStaff_All.ID, qryStaff_All.Nickname FROM qryStaff_All UNION (SELECT "*" AS ID,"<ALL>" AS Nickname FROM qryStaff_All) ORDER BY qryStaff_All.Nickname;
VB Code in Combobox Search Function: Public Function fCboSearch(vCboSearch As Variant) If IsNull(vCboSearch) Or vCboSearch = "" Then fCboSearch = "*" Else fCboSearch = vCboSearch End If End Function
For those that have noticed I have a "Or is null" criteria on the AccountManager field in the result set. I want to take this out and allow the value "Null" to be selected on specifically.
My continuous form (frmThisDate) is based on a query. The table has, among others, a field called 'PODate' which is a date field.
I like to have a box on the form in which I can put a date. Then the records are being filtered by day and month only. So, when I put in 04/29/2005, I like to get all records for 04/29 regardless of the year.
i have a form with three Option groups and one combo box and i am trying to filter it out to the subform that i have on the same form. here is the code, everytime i try to click on Apply Filter. its not working. my filter is not showing on the subform.
Dim strmarket As String Dim stradvertisers As String Dim strheading As String Dim strudac As String Dim strfilter As String
If IsNull(Me.txtmarket.Value) Then strmarket = "Like '*'" Else Select Case Me.fraMarket.Value Case 1 strmarket = "Like '" & Me.txtmarket.Value & "*'" Case 2 strmarket = "Like '*" & Me.txtmarket.Value & "*'" Case 3 strmarket = "Like '*" & Me.txtmarket.Value & "'" Case 4 strmarket = "= '" & Me.txtmarket.Value & "'" End Select End If
If IsNull(Me.txtAdvertisers.Value) Then stradvertisers = "Like '*'" Else Select Case Me.fraAdvertisers.Value Case 1 stradvertisers = "Like '" & Me.txtAdvertisers.Value & "*'" Case 2 stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "*'" Case 3 stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "'" Case 4 stradvertisers = "= '" & Me.txtAdvertisers.Value & "'" End Select End If
If IsNull(Me.TxtHeading.Value) Then strheading = "Like '*'" Else Select Case Me.FraHeading.Value Case 1 strheading = "like '" & Me.TxtHeading.Value & "*'" Case 2 strheading = "Like '*" & Me.TxtHeading.Value & "*'" Case 3 strheading = "Like '*" & Me.TxtHeading.Value & "'" Case 4 strheading = Me.TxtHeading.Value & "" End Select End If
If IsNull(Me.CboUDAC.Value) Then strudac = "like '*'" Else strudac = "='" & Me.CboUDAC.Value & "'" End If
strfilter = "[market] " & strmarket & " and [advertisers] " & stradvertisers & " and [heading]" & strheading
With SubForm![frmMeterAdSubform] .filter = strfilter .FilterOn = True
I wanted to take it a step further and give the user the option to find records that contain FilterBY I tried the following code but got a type mismatch error
I have a form which runs a piece of code on close.
The code:
Code:Prior to this code I populate with a recordset "rstChkConfirm"strEmail = rstChkConfirm("EmailContact")DoCmd.SendObject acSendReport, "rptConfirm", acFormatXLS, strEmail, , , "Trade Confirms from Kyte Options" & date, , No, False
I want the report "rptConfirm" to filter on open with the string "strEmail" Code:Private Sub Report_Open(Cancel As Integer)'Filter report by the email from the previous recordsetMe.filter = "EmailContact = '" & strEmail & "'"Me.FilterOn = TrueEnd Sub
Now the filter does not work, I presume it's because the strEmail is somehow not recognised by the new VB that has opened in the new report doc. Any ideas how I can make it a global var?
I have a General Info form (Form 1) which gathers the info for a specific project. On that form I have an invoice button that pops up the Invoice Form (Form 2) that gathers all the invoice info for the main file in Form 1. Now, I may have more than one entry in Form 2 for the same record in Form 1. How can I apply a filter that will only show records associated to the record specified in Form 1 in Form 2.
Example:
In Form 1, I am at record A and I press the invoice button. How can I only show the records associated to record A in Form 1 in Form 2.
I created a Form in a database that has text boxes relating back to a table. The form also has subform that shows the records from two other tables related to the First. There is a many to one relation back to the main table. As I scroll through the main table the corosponding records in the Subform show up....Everythign is working great.
What I want to add is a Filter to filter the amount of records down.
I am thinking of a button that opens another form. THis form will have a few dropdowns and text boxes which the users can input values These values will then be used to create the filter. Once the filter is ran I need a List box on the main form to pop up with the returned records Finally when the user selects one of these records in the List box the record will be represented in the Main Form.
Does anyone have any examples of how to do this....
I can create the other form adn add the dropdowns.....I just need help creating the Filter and how to get it to Return the records to a list box in the main form and then connect a selection in the listbox to the main form...
Hi All, Just after a code or way to filter client details via a combo box. Basically select combox drop down menu select the sales rep then it filters by sales rep and shows only the clients for that sales rep. I can get it to filter but it does the first client of that sales rep but not the rest.
I want to records in a combobox of a form. I want to filter by the record of that combo (Id) when selected and display it. This is the AfterUpdate code:
Private Sub somecombo_AfterUpdate() DoCmd.ApplyFilter , "Id = Forms!basedata_trial!somecombo" Me!somecombo.Requery Me!somecombo.Enabled = True End Sub
My problem is, though i am using 'remove filter' from the menu, actually the Id is not changing, so the other fields remain unchanged.
I am relitively new to access but I am learning ;)
I am creating a database that will hold details of hand held scanners that are sent out to various customers. This records the product and serial number etc. I have got the products and supplier tabs that I have specified on the 1st form "end_user" working fine and put in some basic error checking such as locking fields. The orders form is to show all the orders that were placed by a particular customer, refered to by "end_user_number (autonumber) primary key" and "end_user_description (Text)" I can now add orders to the database fine and have managed to get the subform to refresh and show details of the newly added order by tagging the requery command onto the end of the save button, so the record gets saved and then the subform refreshed. The problem: I just cannot figure what I am doing wrong! The subform in order history displays all the orders from all the users where I want to filter the orders that the current end_user_number is related to. I have tried using the child filter and master filter with no luck and am trying to code a VB line to filter instead (currently a temporary command button on the ordershistory subform) but I am not sure of the syntax. Any ideas greatfully recieved! :)
