Modules & VBA :: Search Form Will Not Return Exact With Wildcard SQL
Jul 4, 2014
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.
Table structure:
tblMain
ClientID
Surname [text]
Organization [text]
ProgramTitle [text]
City [text]
State[text]
Zip4 [text]
Telephone [text]
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
Code behind search form:
Code:
Option Compare Database
Option Explicit
Private Sub cmdShowallRecords_Click()
Dim strSQL0 As String
Me.txtOrganization = ""
Me.txtProgramTitle = ""
Me.txtSurName = ""
strSQL0 = fSQL_SelectFrom & fWhere & fSurName & fOrganization & fProgramTitle
Me.DS.Form.RecordSource = Left(strSQL0, Len(strSQL0) - 5) & ")"
[code]....
View Replies
ADVERTISEMENT
May 21, 2015
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?
View 1 Replies
View Related
Sep 3, 2005
Background:
I have a database that keeps track of containers -what they belong to, and what is in them. Each container has a “Call #” which is labeled on the container and is a primary key in the database. The “Call #” field is set to only allow 5 characters in this field. A standard “Call #” for example is "A 152".
When a new container comes into the system, which has not been physically labeled yet, I enter it in the database a little different. So let us say container "A 152" just came in, and has not been physically labeled yet. I enter the “Call #” into the system as "MA152". The "M" tell me it still needs to be physically labeled.
When I go to label all the unlabeled containers, I print a report with all the "Call #''s that start with "M".
I have another field in the same table called "Mark". In my forms I can then go through and click a "Mark Button". The Report prints all containers where the "Mark" field = yes(True). This feature is used for a number of different reports, not just unlabeled containers. To keep the database clean and in order, all “Mark” fields reset to no(false) whenever it opens up.
Because, I used the “Mark” field for other reports, simply clicking the “Mark Button” when I enter the new container into the system will not work.
My Question:
Is There a way to have a Query search for the first letter in a field. So when I go to print my report, a query runs looking for all "Call #s" that start with the letter “M”.
View 2 Replies
View Related
May 27, 2015
When trying to match people's names - sometimes with a middle initial, sometimes without - I'd like to try to wildcard each side of it.
So this
Code:
varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME Like '" & varString & "*'")
but also something like this.
Code:
varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME* Like '" & varString & "'")
View 8 Replies
View Related
Jul 18, 2013
Access 2007
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()
[Code] .....
View 2 Replies
View Related
Nov 19, 2014
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
But this hasn't worked
View 4 Replies
View Related
Jan 9, 2014
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.
View 14 Replies
View Related
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:
Like "*" & [Forms]![Myform]![Mycontrol]&"*"
What am I doing wrong?
View 8 Replies
View Related
Apr 17, 2007
Ok, i have a search query as follows
SELECT Table1.*, Table1.[Winning Contractor]
FROM Table1
WHERE (((Table1.[Winning Contractor])=[Who was the winning bidder?]));
This works well except if the user doesn't captialize a letter or doesn't type in the full company name. Is there a way to change this search to a like search instead of an exact one?
View 6 Replies
View Related
Jun 17, 2005
How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's
This shows my query at present.
Like [Forms]![Search]![BoxNo] & "*"
View 4 Replies
View Related
Aug 23, 2013
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;
But am still getting "#Error" in the combo box.
View 2 Replies
View Related
Jan 9, 2014
Access 2003
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
View 9 Replies
View Related
May 31, 2007
Hi,
Does anyone know how to perform an exact match using file search. The code I have below searches for the users AcroRd32.exe file (I have done it this way to allow for different versions of the reader). The problem is that foundfiles returns also AcroRd32info.exe ie FoundFiles(1). i only want to search for the prior!
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:Program FilesAdobe"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
If .Execute > 0 Then 'check if there is such a file
stAppName = .FoundFiles(1) 'then define this as the applicate string name
stAppName = stAppName & " " 'append a space or file string not correct
'specify path name for location of file.pdf
stlocation = GetPrivateProfileString32("C:WINDOWSSSI_PROGRS_DATABASE.ini", "DIR", "DATABASE_FILELOCATION")
stpathname = stlocation & "file.pdf"
Shell stAppName & stpathname, vbMaximizedFocus
Else
'if no file is found then need to instruct user to install reader
MsgBox "You need to install Adobe Acrobat Reader to open this file", vbExclamation
End If
End With
Cheers to any help..
Tania
View 2 Replies
View Related
Jun 13, 2005
I am working on a form that will be used to search records in a table. I have created the query shown below:
SELECT tblSTC.STC, *
FROM tblSTC
WHERE (((tblSTC.STC) Like forms!frmsearch!txtsearch));
I want my user to be able to enter search criteria and allow the use of a wildcard. When i run the query no data is found??
Can anyone help, thanks.
View 3 Replies
View Related
Jun 3, 2013
All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code:
Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
View 13 Replies
View Related
Sep 8, 2005
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
End Sub
the code stops at the line:
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
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
View 9 Replies
View Related
Sep 21, 2012
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
View 1 Replies
View Related
Jul 31, 2014
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.
View 3 Replies
View Related
Dec 12, 2014
I am working with the Access Treeview feature and I'd like to align the different elements in my nodes exactly below each other.
Therefore I need exact distances (if string is longer then XY, cut short, if shorter, extend to XY).
Of course I cannot work with len() and fill with spaces because letters have different widths.
Is there a way to determine the exact width of a string? e.g. in pixels?
View 7 Replies
View Related
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
View 3 Replies
View Related
Feb 21, 2014
I'm trying to add a spreadsheet from a folder using wildcards by just looking for a number in the file name.
strPath = "C:Training"
strFile = strPath & "*" + "(ZZ131008)" + "*.xls"
DoCmd.TransferSpreadsheet acImport, , "Training1", strFile, , ""
I get a response saying it cannot find the file and it shows the * in the path it cannot find. How can I make it find the right file?There are more files in the folder and the (ZZ131008) defines that course, I'd like to reuse the code for the other courses too.
View 3 Replies
View Related
Oct 17, 2013
I have:
Code: Kill "Z:Client_Reports" & [websiteID] & "" & LEFT([Forms]![frm x main]![month name], 3) & "*"
This has been working fine - all the files that meet that criteria are deleted, but now I need the option to exclude a particular file from those to be deleted: the particular file would be identified by having some specific text in its filename. So for example I need to be able to delete all files meeting the criteria EXCEPT any files that have the string "summary" as part of the filename.
View 5 Replies
View Related
Jan 29, 2014
I want to my ImageFrame to open as a picture. My problem is that my picture could be either a .jpg or a .png (or whatever). The code below works if I hard code profile_pic.png or profile_pic.jpg. Is there a way to use the wildcard here? (Its not working as written now)
Code:
Dim vFolderPath As String, strFile As String, dirFile As String
vFolderPath = DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")
dirFile = Dir(vFolderPath & ctrl_people_id & " *", vbDirectory)
strFile = "profile_pic." + "*"
[code]...
View 8 Replies
View Related
Jun 28, 2013
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=" & [CustomerID])
The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".
View 8 Replies
View Related
Apr 25, 2007
Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!
Private Sub Command4_Click()
Dim stAppName As String
Dim stPathName As String
Dim fs As Object
Set fs = Application.FileSearch
With fs
.LookIn = "C:Program Files"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
.Execute
????? stAppName = .FoundFiles
Set fs = Nothing
End With
'specify path name for version of adobe acrord32.exe
'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe "
stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf"
Shell stAppName & stPathName, vbMaximizedFocus
End Sub
View 1 Replies
View Related
Mar 7, 2006
Hi,
In my search form I have 5 creterias and in the query behind it I joined them by AND. the problem is if the user entered only 2 or 3 of those creterias, the query will not return anything.
how can I slove that?
your responses are very appreciated.
Regards,
CS.
View 2 Replies
View Related
