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.
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
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.
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 & "'")
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] .....
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?
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 RelatedI 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]....
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
But this hasn't worked
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.
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
i've try and no luck... using Like "* *"
i'm tryin to create one that if i search for like let say "brisbane" it would show the results of the complete spelling, but let say if i was to just type "b" or "bris" it would show u a list of "B" towns or matching words of "bris".
any assit would be great
I have a field in a table that has the following data in it:
W-01-2005
W-02-2005
W-03-2005
W-04-2005
etc..
I created a query against this table and put a pop-up on this field so they can enter the search criteria needed, or leave blank for all.
I'd like to modify this pop-up code to ignore the W- and -2005. When the pop-up appears and asks them what week they want, I'd like for them just to enter 01 or 02 or 03 etc... not W-01, W-02 etc..
This is the code I have currently in this field.
Code: Like [Enter Two Digit Week] & "*"
Thanks, Leap!
Can anyone say what the syntax would be for the "incorrect" statement below?
Like *Mid([checking]![COMPANY NAME],1,InStr([checking]![COMPANY NAME]," "))*
As the statement above doesnt work, just returns errors.
Thanks in advance
Hi,
Im having a little problem with the following code in one of my queries
Expr1: IIf(IsNull([Forms]![editLookForm]![Company_Name]),True,[Company Name]=[Forms]![editLookForm]![Company_Name])
What this does is, it take an typed entry from the form (editlookform) and seaches for that entry in the query
i want to add wildcard entrys to this so that the person operating doesnt have to know the complete company name - i just dont know where to put them
Help please
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.
I have several queries looking up products from a table. I use a simple form to access the query and in the criteria of the field I use [Forms]![EnterCroft]![ProductName] to filter by name. (EnterCroft is the name of the form).
It works fine but doesn't like the asterisk (*) for all products.
I also want to use wildcard before and after key words in the product names, eg *cup* for anything with CUP in it's name.
How can I do this?
Thank you very much.
ok in access 2003 i have tried everything to get the Like "*oak*" to work but to all fail it replaces "Like" with "ALike" i have tried to find some info on this but have not it seams like something easy i wanted to do.
SELECT MainData.ModelName
FROM MainData
WHERE (((MainData.ModelName) ALike "*oak*"));
some reason it wont find all data with "oak" in it just returns empty
this is like query 101 but for some reason it has stumped me, along with one other problem but i am still researching that one.
This is making me mad now :mad: i want a query to search for what a user inputs into the query. but for example if i seach in a field wheres there is one or more words in it. eg. I want to search my table for a address (60 donnington road etc) so in the user input box i type "donnington" but i get no results even though it should find "60 Donnington Road"
code i used. [Please enter address] now where does the wild card go?!?!
hope that makes sense :cool:
thanks
Hello, I'm trying to use wildcards to filter for records that contain a date somewhere within the string.
I'm using Like "*#.##.##*" which works for dates that have a day of the months >10, but what about those under?
Well, Like "*#.#.##*" only works for those with the day of month <10. Is there a wildcard character for "one or more digits"?
Also, the above example excludes Oct. - Dec. for the same reason.
help!
Thanks,
Gifford
Hey everyone, I've been busy creating a switchboard based on database, and I came across this issue. When I put a search through records command button, it seems that I can't use the wildcard characters, such as *,%,_ in order to find multiple records matching my criteria.
Am I supposed to "enable" them from somewhere or am I doing something wrong.
Any help?
Thanx in advance
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.
I have a combobox with 2 columns where I manually set the following criteria:
"E";"English"
"F";"French"
"EF";"Bilingual"
In my employee table under the Languages field I have E,F or EF
I wanted to display in a sub-form a list of employees who speak a specific language but I can't use a wildcard in the combo-box properties. Basically when someone is looking for an English speaking employee they should get both E and EF employees not only E.
I tried doing "E*";"English" but it doesn't display anything.
I have a query that prompts with a parameter box for the field Contributor_ID. Contributor_ID is a numeric field & Key and I use it to allow the user to print a report based on which Contributor_ID value the user enters. This works fine. However, I would like the user to have the option to enter * and then have the report include ALL the contributor IDs. I have done this before on a text field without a problem, however is this not possible on a numeric field as it is not working? I'm trying not to have another version for the ALL option.
If this is not possible, are there any alternatives?
Hello,
I am not sure if this is possible, but I am running an update query to add text in a field if a certain criteria is met. However, if the field I am adding to already contains what I am trying to add, i don't want it to add it again. Everything works fine except the IIF statement in my update field. Here is what I have:
IIf(Temp_Vendor_Trips.[Reco Classification]="*" & "V3" & "*",Temp_Vendor_Trips.[Reco Classification],Temp_Vendor_Trips.[Reco Classification] & " " & "V3")
If the field contains the text V3, I want it to be left alone and continue to show what is already in the field, if not, i want it to add V3 to the end.
Thanks in advance for the help!