Modules & VBA :: Form With Multi-criteria Searches / Uses Strings And Filters
Jan 23, 2014
I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields. As of now, it works as long as all four criteria boxes aren't null.I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.
Code:
Me.Filter = "[Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) & "
AND [HR Holder] = '" & Me.HRCombo & "'" & " AND [Building] = '" & Me.BuildingCombo
& "'" & " AND [Room] = '" & Me.RoomCombo & "'"
Me.FilterOn = True
Me.Requery
I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end. Here's the code I used for this, and, with my limited knowledge, I can't get it to work.
Code:
Dim StrA as String, StrB as String, StrC as String, StrD as String
If Me.Keyword is null then
StrA = "*"
else
StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34)
End If
[code]....
View Replies
ADVERTISEMENT
Mar 25, 2015
I know in Access that you can filter your work load criteria for each employee which is fine using Como boxes to filter down specific criteria for that employee, however I'm trying to achieve it with date filters between certain dates, and it works but ends up filtering the dates for every employee, I just want it to filter that specific employee .
This is what I have so far.
PHP Code:
Private Sub Date_Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mm/dd/yyyy#"
If Not IsNull(Me.txtStartDate) Then
[Code] ....
View 2 Replies
View Related
Jul 21, 2014
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#dd/mm/yyyy#"
[Code] .....
View 1 Replies
View Related
Jan 12, 2015
I am designing a search query that will allow the user to look up a record in a database to view it. I have everything already set up, and most of it working properly.
The user can recall a database entry using 7 different criteria--Type, Customer, PartNo, JobNo, Warehouse, Bin, and Shelf. The Type and Warehouse entries on the database are drop-down values, the other 5 values are text entries.
So far, I have been able to get the look up query to pull up the desired records on the Customer, PartNo, JobNo, Bin, and Shelf criteria using
Code:
Like "*" & [Forms]![Search Form]![Customer] & "*"
.
However, with Type and Warehouse criterion, the two that use drop-down boxes in the database, I have been unsuccessful in being able to call up any records using either the above partial or the more exact:
Code:
=[Forms]![Search Form]![Type]
I did try to change Type to a textbox on the look up query, but that was similarly unsuccessful. On a side note, I must use drop-downs on the Warehouse field since I have another query that concatenates that value with a couple others.
How can I, without delving into VB coding unless absolutely necessary, format the lookup query so that it will read the values of the drop downs?
View 7 Replies
View Related
Oct 16, 2014
Me.Text11 = Nz(DAvg("[final whse-in diff]", "dbo_inventory", "[CAFETYPE]=" & Me.Text7 And "isnull(me.[DATE FIXED])=" & True And "isnull(me.DATE_IN)=" & True), 0)
I am getting a type mismatch error with this.
My question is:
1. is the syntax correct....
2. Is my way of checking for a value to be null correct....
View 4 Replies
View Related
Dec 4, 2013
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
View 6 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 Replies
View Related
Aug 17, 2007
Hi all:)
Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form.
I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this
Thanks Jackie
View 3 Replies
View Related
Dec 7, 2011
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
View 3 Replies
View Related
Aug 12, 2014
I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.
I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.
Lets say we run a hairdresser.
I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.
Example 1:
Each customer has a budget to spend on haircuts.
Each hairdresser offers haircuts from $x to $y.
The query should return all hairdressers that are appropriate for the budget of the selected customer.
There should be a yes/no button on the form to ignore or use this criterion.
Example 2 (this completely threw me off):
Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring).
Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying).
The customer and hairdresser table use the services table and a multi combo box to select the services.
The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.
To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".
View 7 Replies
View Related
Jun 20, 2014
I have a table that I would like to run multiple filters and criteria.
I would like to have the user enter a word that matches a specific word that matches a field name and all the data fields = 1.
i.e user inputs word baa baa and the query criteria is set to one, it will output all the surveys that match that.
View 14 Replies
View Related
Sep 16, 2014
I'm trying to add a search function the searches with ever letter I add to the string in the search box. if the string is not in the recordset then vbred the textbox.
Here's my code:
Private Sub txtGroupNr_KeyPress(KeyAscii As Integer)
Set RstRecSet = Nothing
Set db = CurrentDb
On Error Resume Next
If IsNull(txtGroupNr) Or txtGroupNr = "" Then
' MsgBox "Please enter a Group Number to use as the search criteria", _
[Code] ....
View 4 Replies
View Related
Jun 6, 2007
Hello
I have two tables with names of people in different forms.
table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname
table2: [surname]
I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]
I've tried using
Like "*"&[surname]&"*"
This returns everything rather than the similar names.
I think this should be simple but can't work it out.
View 1 Replies
View Related
Aug 30, 2014
I'm trying to create a button that will export the filtered records on the screen to an Excel file.
I'm using strWhere as my where string and found this code in one of the posts from this forum, but unfortunately, I can't get it output only the filtered records. It outputs all records instead.
Dim db As dao.Database, qdf As QueryDef, mySQL As String
Dim strWHERE As String
Const strSQL = "SELECT * FROM [Action Register] "
[Code].....
View 11 Replies
View Related
Mar 21, 2015
I have a set of combo filters that filter one after the other as follows –
Code:
Private Sub cboCity_AfterUpdate()
If Nz(Me.cboCity.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
[code]...
and so on to filter down so the user can work with what they filter, my question is how can I add on a filter that filters between dates? and second I was hoping that I could program the filters so that they could be changed individually/randomly as to filtering one after the other and then clearing to restart the filtering again if that makes sense. I have tried using this, but it doesn’t work ‘Bad Command’
Code:
Me.Form.Filter=”StartDate =#” & Format(Me.txtStartDate, “mm/dd/yyyy”) & “#”
And
Code:
Me.Form.Filter=”EndDate =#” & Format(Me.txtEndDate, “mm/dd/yyyy”) & “#”
View 4 Replies
View Related
Apr 17, 2015
I have a form where I have buttons that apply filter to a certain column.How do I enable multiple filters where I can click more than 1 filter button and it keeps the filters?
First filter button:
Private Sub Command1_Click()
DoCmd.ApplyFilter "Filter1", "[MyQuery]![Checkbox1]=Yes", ""
End Sub
Second filter button:
Private Sub Command2_Click()
DoCmd.ApplyFilter "Filter2", "[MyQuery]![Checkbox2]=Yes", ""
End Sub
Third filter button:
Private Sub Command3_Click()
DoCmd.ApplyFilter "Filter3", "[MyQuery]![Checkbox3]=Yes", ""
End Sub
It works well, but one by one. How can each next filter be added to previous filters by clicking filter button on a form?
View 2 Replies
View Related
Mar 12, 2015
I have a set of combo filters that filter one after the other as follows -
If Not IsNull(Me.NameFilterBox) Then
If Me.Form.Filter="" Then
Me.Form.Filter="Name ='" & Me.NameFilterBox & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and Name = '" & Me.NameFilterBox & "'"
[Code] .....
and the I use the
Me!Form.Filter = Me!Form.Filter & " and Name = '" & Me!cboOPOwner.Text & "'"
to filter down so the user can work with what they filter, my question is how can I add on a filter that filters between dates? and second I was hoping that I could program the filters so that they could be changed individually/randomly as to filtering one after the other and then clearing to restart the filtering again ...
View 11 Replies
View Related
Sep 11, 2013
I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
View 4 Replies
View Related
Mar 9, 2014
I have created a module, where based on various selections (form), the output is thrown in the table for editing various fields. This works fine with single user. But once I have placed the same database on the share drive for multiple users, the users are unable see the data in the text filters. I don't know what is the issue all about. Also if i use me.requery, the text filters becomes blank. Below is the code :
Code:
Option Compare Database
Option Explicit
Public Function SelRec(shDate As Date, ATMID As String, City As String, Depots As String, Vendor As String) As Boolean
SelRec = False
[Code] .....
View 12 Replies
View Related
Jul 20, 2015
Small piece of code that can send / recieve small text-strings over a network. I finally got it to work, but forgot to think ahead . Right now it only works in a formular but i really need to be a function with input / output.
My problem is that i am using the "Withevents" to call the Ostrosoft Winsock network module and "Withevents" does not work in functions.
How to build this into a function ?
Code:
Option Compare Database
Option Explicit
Dim sBuffer As String
Dim spage As String
Dim WithEvents wsTCP As OSWINSCK.Winsock
[Code] ....
View 2 Replies
View Related
Oct 21, 2013
I have a field in a table that I'd like to extra the texts from. The are stored in below format (separated by colons). They don't always the same amount of characters and not all of them have the same amount of texts.
Some may only have Text1:Text2::, while some may have Text1:::Text4.
Text1:Text2:Text3:Text4
View 8 Replies
View Related
Apr 24, 2015
I have a report which is opened using a DoCmd.OpenReport. There's a criteria string which filters the main report - this works fine.
There's now a requirement to place a summary subform at the beginning of the report, in the report header. I need that summary to use the same criteria string as the main report.
For the main report's OnLoad I put : Me!Expenditure_By_Type_Subreport.Report.Filter = Me.Filter
But I get the error message:
Error 2101. The setting you entered isn't valid for this property.
I tried it the other way round as well - in the OnOpen of the subreport I tried : Me.Filter = Me.Parent.Filter
And it gives the same error.
When I just a manual Filter change such as : Me.Filter = "Project_ID Is Not Null"
View 3 Replies
View Related
May 4, 2006
I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...
View 1 Replies
View Related
Jul 22, 2013
I have a Excel spreadsheet with an embedded query that pulls from an Access table. The users use a spreadsheet with an embedded query to search on a field called "Circuit ID", to bring up results showing the history of a given circuit id. Currently they have to enter an exact match, for example if the circuit id value is DHECHUIOY, they need to enter this exact value DHECHUIOY. They want instead to be able to enter DHEC and a wildcard character to bring back every value that begins with DHEC. There doesn't seem to be a way to do this in a spreadsheet with an embedded query.
I finally decided that it would be easier to create a form in Access
How I can create a form which allows users to search on a value using wildcard characters ? (the user enters the wildcard character).
I'm on Access 2012, Windows Vista
View 1 Replies
View Related
Jan 3, 2008
Good morning everyone,
I use this code to filter a subform with multiple criteria.
Private Sub Filter_Click()
Dim strWhere As String
If Not IsNull(Me.Coordinator) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[EmployeeID] = " & Me.Coordinator & ""
End If
If Not IsNull(Me.Customer) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[CustomerID] = " & Me.Customer & ""
End If
If Not IsNull(Me.Supplier) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[SupplierID] = " & Me.Supplier & ""
End If
Me.Track_All_Orders.Form.Filter = strWhere
Me.Track_All_Orders.Form.FilterOn = True
End Sub
This Code works great, however i want to use it to filter my report as well, so I put strWhere as a global var and i wrote this code to generate the report with the same filters criteria as the subform:
Private Sub cmdGenerateReport_Click()
Dim stDocName As String
stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Nothing happen when i click on cmdGenerateReport!! any clue?
Million thanks in Advance,
Best Regards,
View 2 Replies
View Related