Queries :: Wildcard Search Does Not Work With A Form Control
May 24, 2015
I am trying to get a wildcard search to work with a form.
I have a query, in which the criteria is:
Like "*" & [Enter a word] & "*"
That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:
I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this. My filter criteria for the text based combo boxes are like this:
Code: Like "*" & [Forms]![ReportDesignF]![Company] & "*"
My filter criteria for the Start and End Dates looks like this:
Code: Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#
In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.
How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?
I did export the table data into a tabbed delimited format and will include that at the end. If you want to reproduce my bug copy that data in a txt file and import that into the table tblMain. Make a search form and a sub form. the sub form is linked to the table and the main form is unbound with two search buttons.
Problem Statement:The code works fine. I did find a bug that seems to arise with the wild cards when the entire field values are entered. You can replicate the bug by testing the search criteria listed below.
This is a brief example of the bug. A detailed description is near the code below.
If my name is "Devtycoon" and I search "Dev" the SQL statement will build "*Dev*" and it will pull up my name, "Devtycoon". On the contrary if I search "Tycoon" the SQL statement will build "*tycoon*" and it will pull up my name, "Devtycoon". If i search "DevTycoon" the sql statement will build "*Devtycoon*" and no results will be returned. That is buggy because the name is in the database but no wildcards would be needed.
Form1 contains the controls for search criteria. Three text box controls are used to filter a sub form control called "DS". The sub form is called sFrmMain and is a datasheet that shows results of the search. there is a button that runs the code and another button that clears search criteria and shows all records. Both button's code set the sub form's record source using an SQL string built using a function that returns a segment of the overall search string using the contents of each control that then is concatenated into a SQL statement used to set the record source.
There is a function for the following components of the SQL statement
SELECT / FROM WHERE controlA = me.txtSurname controlB = me.txtOrganization controlC = me.txtProgramTitle This is how you can replicate the bug.
I tested two additional surnames organizations and program titles as follows:
Try example (1). you will get both the 4's and the 14's records returned.
If you type in letter for letter of the second record (the one with the 14's) no records populate. It is like the wild card does not like it if you type in the entire field value. Uncle Gizmo's and Allen Brown's method do the same thing were no records populate if the 14's entire surname organization and program title are entered into the text controls. Can you reproduce this error? Other than that I think either method is bulletproof.
Example criteria
1) put the following criteria in each control then hit the search button
4 surname 4 organization 4 program title [two records returned]
2) put the following criteria in each control then hit the search button
[no records returned] 14 surname 14 14 organization 14 14 program title 14
I'm trying to make a filtered search form using "*" as a wildcard default value in combo boxes, this works for all the text fields except for the account number field (Numeric primary key). After quite a bit of reading up and searching, I tried using the following as the row source;
SELECT customers.ACCOUNT_NO, customers.CUSTOMER FROM customers; UNION SELECT "*", "All" From Customers;
Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName
This code works but, I'd like to make it case insensitive
Dim strFilter As String strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*""" Me.Filter = strFilter Me.FilterOn = True
Hi, I'm making a query which is used to generate a report. Anyways, I've ran into a problem. The query is based on some controls in a form, but what I would like to do is that if a control was null then a field in the query should be assumed to be a wildcard for the criteria, otherwise the data in the control should be used in the criteria of the query. Does anyone have any ideas if this could be done?
* This code works to search frmMember for criteria found in tblMember:
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
[Code] ....
* But this next code which I have copied and Pasted to frmEventInput, then renamed the frm and tbl, retrieves the same criteria as the above (tblMember) instead of the tblEventInfo...
Private Sub cmdSearch_Click() If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then MsgBox "You must select a field to search." ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "You must enter a search string."
first i must thank everyone here. this forum has proved invaluable for some of the simple databases i have created. now i have a problem which i cannot find the answer for. i'm using access 2000 basically i am creating a cemetery database so most of the information i'm dealing with is duplicated. to create a unique record i have six primary keys on the one table. ie people with the same name, age, and birthday can be buried in the same grave. anyway i need to do a wildcard seach from a form (QBF) based on surname and firstname. i have downloaded the code below and modified it:
Private Sub cmdsearch_Click() 'Set the Dimensions of the Module Dim strSQL As String, strOrder As String, strWhere As String Dim dbNm As Database Dim qryDef As querydef Set dbNm = CurrentDb()
'Constant Select statement for the Query definition strSQL = "SELECT searchtestdata.Surname; searchtestdata.Firstname; " & " FROM searchtestdata"
strWhere = "WHERE"
strOrder = "ORDER BY searchtestdata.autonumber;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form If Not IsNull(Me.txtsurname) Then '<--If the textbox txtFName contains no data THEN do nothing strWhere = strWhere & " (searchtestdata.Surname) Like '*" & Me.txtsurname & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef End If
If Not IsNull(Me.txtfirstname) Then strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND" End If
'Remove the last AND from the SQL statment strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query Set qryDef = dbNm.QueryDefs("quesearchtestdata") qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
'Open the Query DoCmd.OpenQuery "quesearchtestdata", acViewNormal
and tells me either that i have "run-time error 3142 - characters found at end of SQL statement" - this is the error i am recieving at the moment, or more usually " run-time error 3141 - which tells me the SELECT - WHERE statement is wrong. i am definetly no expert in VB - i am only just starting using it. so guys any idea? thanks in advance
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search() Dim lssql As String Dim lsSn As Recordset Dim db As Database Dim lsMessage As String Dim sMsg As String Dim vRetVal As Variant Set db = CurrentDb()
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
I have a form which has a combobox called Task_Ref which looks up values in a table column.
I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-
Test number 1 Yesterdays Test
As long as the word "Test" appears I would like the above to happen?
I was thinking of something along the lines of:-
If InStr(Task_Ref.Value, "Test") > 0 Then P1.Value = True Else P1.Value = False End If End Sub
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.
I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.
The following hard coded query returns two records, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1) Like 'BRD*'));
I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));
When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.
I've tried all variations of the LIKE statement in the form field, but nothing works.
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date FROM [rpt LEAD TYPE SUCCESS RATE2] GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
I am running a query that links to a "wildcard" form so that the user can basically run a query filtered on any field they want.
For some reason when I try to use wildcards along with Criteria in my query the query will not return any results. I know the link to the textbox is right because if I take out the wildcard and put an exact word from the table I get a result.
I have tried using many different combinations inclusive of the following:
Like [Forms]![Refurb-WildcardReport]![RefurbWildcard-Name] Like "[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]" "[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]" [Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]
Any help would be appreciated, as always thank you ahead of time.
Hi I am looking for ways to sort date columns. Something like find all in one particular month, or year.
I know i have done this before to sort data into monthly queries, but now i have forgotten >> something like ="#*/7/*#" to sort all date on the 7th month.
By the way I cannot seem to get access to format dates in the dd/mm/yy format, does anyone know if there is an option for this.
Hi there people, i have created a query that searches through different fields within the database, and on its own works fine, enter some or no data and it will return what you have entered. The problem is i have created a form with text and combi boxes on that i can enter the query parameters into in one place, but i can not get the search button to use this data and filter the query? the query is a subform on the main search form and is refreshed when the search button is pressed but with no filters defined by the text/ combi boxes. can anyone help please?
I have a quick search on my site and it wont work any more and I cant figure out what is wrong with it? the search uses two check boxes, one for cards and one for comics and all the info is in an access bd with two tables. I'm totally new to all this so I cant figure out what I did wrong here is the form <TD ALIGN="CENTER" HEIGHT="89" WIDTH="330"><font size="4" font color="#FF0000"><b>Quick Search of our Inventory</b></font> <FORM NAME="searchform" ACTION="./html/quick_search_results.asp" METHOD="get"> <INPUT TYPE=TEXT NAME="searchvalue" VALUE="" SIZE=18 MAXLENGTH=26> <INPUT TYPE=SUBMIT NAME="search" VALUE="Search"><br> <INPUT TYPE=CHECKBOX NAME="card" onClick="uncheckcomic()"><FONT COLOR="#FFFFFF">Cards</FONT> <INPUT TYPE=CHECKBOX NAME="comic" onClick="uncheckcard()"><FONT COLOR="#FFFFFF">Comics</FONT><BR> </FORM> </TD> The query string is <script> <!-- function uncheckcomic(){ document.searchform.comic.checked=false } function uncheckcard(){ document.searchform.card.checked=false } --> </script>
CAN SOMEONE PLEASE HELP ME WITH THIS, I'M TOTALLY CRACKING UP TRYING TO FIGURE THIS OUT
I need to implement a Quick search feature in my employee application.
So that when any text is typed into the textbox, it searches the db for a match:
e.g. firstname lastname ss# employeeId deptId officelocation deptname
It returns the record in the employee table that consist of the text typed in the search box. Most websites have this quick search feature (blanket search..is that what it's called?) and a link for advanced search, where they can select specific fields to search.
In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.
The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.
I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".
I tried using the criteria:
Code: Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.
I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".
i have a search form (see atachment1) with 2 combo boxes. i have a pruduct form that runs on this query:
Code: SELECT * FROM tblSpeler WHERE (((tblSpeler.merk)=forms!frmZoeken!zoektekst1) And ((tblSpeler.type) Like forms!frmZoeken!zoektekst2));
if i start the the product form, it asks me for the make, and then for the player ŧ it finds the right player in the form. but via my search form it wonīt work. it only opens the product form and no records are shown.
so: in the search form, when i select a "make", and then the "type", and then press search. i want it to show the right record in the product form. + when i select a make (eg "apple"), i want the type combo box to exclude all the types that are not from apple (eg only: ipod mini, ipod 20gb. right now itīs like in atachment2.
i know this is a common question, been trying to figure it out with a eg database, but i canīt get it done. all help is welcome
