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?
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”.
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] & "*"
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
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]....
Hi there,
I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.
I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.
Any help appreciated.
why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.
The dysfunctional code:
Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind
[Code] .....
The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
View 3 Replies View RelatedI'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 RelatedI 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.
View 2 Replies View Related1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
I am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
View 3 Replies View RelatedI have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
View 5 Replies View RelatedAll. using 2010. I have a form and a subform. my master and child links are set but when I scroll thru my form; my subform does not change to show the matching records. I do have an unbound search field for the same field as the master and child links. When I type in the search field for a record; the subform does change to that particular record. Do you think that has anything to do with it?
View 1 Replies View RelatedI have a database that uses four forms and each form has it's own table and each form has it's own search button to find a specific record within that table. I would like to use one search button that will look at all four tables and bring up the correct record when the request number is entered. Can I link all the tables to perform this or is code required? Can someone send me in the right direction for how to do this? Give me an example of code if it's required?
Thanks for any help I can get.
Laura Edmark
So basically, what I have is a bunch of words in one memo field, for example:
dog cat cowboy tree flower
To search it, this is the code I have now.
' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*" * " AND "
End If
The only problem is what I search for needs to be "in order", for example, if I search for dog, I'll get the table. But, if I seach for tree, I won't because tree was not place first. Can you please help me? Thanks a lot!
Hello
I have a main form with a search command button in the header. This search facility looks for information in a subform. At the moment I can't seem to get it to work.
Main form called Building
Subform called LineTypeSub
Field its trying to search is called Line
Here's the code I have at the moment.
Private Sub cmdSearchLine_Click()
DoCmd.ShowAllRecords
DoCmd.GoToControl ("Line")
DoCmd.FindRecord Me!SearchLine
Line.SetFocus
SearchLine = Line.Text
SearchLine.SetFocus
strSearch = SearchLine.Text
End Sub
If anyone could help that would be great, thanks.
My search form has, say, 5 search boxes. Eg.
Vee
Cathederal
RIB
Keel
Lifting Keel
I need to have all the boxes visible and searchable. BUT I only want to search one or two at a time. How do I do this?
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
HI!
I'm trying to figure out, how to make seach form with one text field, that will accept OR statements. I have one table with fields name, category, and description.
Is there any chance to make search like this:
User will put "part of the string" OR "part of the string2".
Result will be the list of the records that include these parts of the strings in any of the given above fields (name,category,description)
I hope i'm clear enough
Thank You in Advance
for any Advice
Hi
Im creating a query where the user specifies a word and it will search for all entries based on that word. The problem is I dont know how to tell the system to Search for the word anywhere in the sentence at the moment it searches for a match at the begining of the sentence. I think an example is neccessary
E.g I want to search for all entries that have an "Area" word in the sentence
My system has values in a column that contain : Picnic Area, Work area etc
My current search will only work if you type Picni or Work
Im using:
Like [Enter Location] & "*"
Help Please?!?! :confused:
Hi,
I need to create a form with a search mechanism that brings up a record as soon as you start typing what you are searching for.
For example:
If I want to search for a name called Bee; as soon as I type B the database should return all names that start with B.
Does anybody know about any tutorials/articles on how to implement this please?
Any help will be very much appreciated.
B
I know this will be very basic but i need help. I have a table of customers. I have set up auto form with the table.
I would like to have a form that allows me to search for a customer.
example:
Enter Customers Surname__________
An this returns customers with this surname.
All help is appreciated
Thanks in advance