SQL To Search Multiple Fields
Jul 24, 2006
I have a search form, and it has 2 combo boxes, and 3 textboxes. I would like one of the textboxes to search 10 different fields for the inputted data. Along with that search all of the others must be able to be used to narrow down the criteria. How can I do this with SQL? Right now I have a select distinct, where, and I use And to include all 5 parameters. What can I do to use the one box to search numerous fields and still return options when coupled with the other search parameters. Thanks
Riley
View Replies
ADVERTISEMENT
May 1, 2006
Hey guys I need some help, I have attached my database.
I need to search multiple fields.
I need to search from field strserialone - strserialtwelve.
Thanks for taking the time..
View 3 Replies
View Related
May 13, 2014
Im trying to make a find page that looks up 7 fields and after reading about the Dynamically search multiple fields i downloaded the sample and changed a few things. But I have a few problems .
1) If you open the FRM_SearchAll from the navigation pane , it will not show the detail form by dbl_click or view button also will not show the picture .
2) If you open it the navigation form , it has the following error
Forms!FRM_SearchMulti!SrchText before you start it.
3) As above it will not show the detail form by dbl_click (it has Error)
Run time error : 3075
Syntax error (missing operator) in query expression '[Food]Snack a Jacks'.
4) If you try to view detail form with the button (the error comes up)
Syntax error (missing operator) in query expression '[Food]Snack a Jacks'
5) also will not show the picture .
View 3 Replies
View Related
Feb 13, 2015
Find the link by tacking on the following to this site's primary [URL] .....
It was, essentially, a type-and-show-as-you-go search functionality for an Access Form. It works great, except that I'm experiencing intermittent slowness when using the form. As I type, the characters appear very slowly and experience a significant lag time.
I only have about 70 records so far, so I'm pretty sure the slowness is not being caused by an overabundance of data. At other times, it goes super fast without any lag.
View 4 Replies
View Related
Sep 2, 2013
I want a search box on my db that will locate data from certain fields within my database. Today i came across the posting by John Big Booty with the above title and what he has done is exactly what i require.
Now i have followed all instructions to the letter however i keep getting an error which i have attached ......
View 4 Replies
View Related
May 21, 2014
The code currently works for searching data on ONE single table.What if I wanted it to search data simultaneously on TWO similar tables? I was messing around trying to build up a query that uses both tables but it failed.
View 3 Replies
View Related
Aug 8, 2006
Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
View 4 Replies
View Related
Dec 26, 2013
I want to make a form which will allow me to get data from a query and search using 3 fields parallel.
Attached the database in 2010 format.
Password of the db is "nolimit".
The query PTM & Equipment should be bound with the form and
If I enter month, PTM and equipment need rest 2 fields displayed.
Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.
View 3 Replies
View Related
Feb 17, 2015
I want to create a parametric search for tools on the shop floor based on multiple fields [using VBA]
I've had a good search and turned up lots of useful things; but each example insofar has been fairly specific as opposed to a guide on how to implement it in a wider setting.
Let us say, for example, that my database has three fields each with a bound combo box: location; house-type; number of bedrooms
As I type in location, I want it to filter my results in the other boxes to that location (should be a simple case of applying a filter?) - which leaves me with all the house-types and bedroom-counts in that location.
As I type in the house-type, I want it to then narrow down the search again to filter the "location"+"house-type" to leave me with the available number of bedrooms.
What is the best way to implement this, given that I have about 20 fields*, some of which will inevitably be blank: as the user types in (or selects using combo boxes) the data required, it narrows down the search - and finally there's a "search all" button which returns all results matching the current criteria.
When I say "implement" I mean what underlying structure should I use. Is it best to create a query and update the query as items are entered, or would it be better to filter the form that's having the data entered?
The fields are organised sensibly into sub-forms for various characteristics (i.e manufacturing data, materials data, cutting data, etc, etc), don't worry - it's note a huge mess - and the code is all there to pull the data together as required!
View 10 Replies
View Related
Nov 24, 2013
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
View 3 Replies
View Related
May 22, 2006
Right what im after sounds complicated but its quite simple.
I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.
Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.
When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.
not understanding fully what i mean?
i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.
http://awilderbeast.com/CWSystem.zip
The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list
Thanks for any help
View 7 Replies
View Related
Feb 19, 2014
"Dynamically search multiple fields" using my own customer data.
I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.
Right now the data is in excel so I have a completely blank page to fill.
View 2 Replies
View Related
Mar 21, 2013
[URL] ....I am trying to dynamically search multiple fields from a combo box on a form that includes a subform. I am using code from the above referenced link within this forum.
It works without the "setFocus" for the Listbox except it jumps to a record after the very first character is entered instead of narrowing down as characters are typed. If I leave in the "setFocus" I get run-time error 2110.
View 14 Replies
View Related
Jun 20, 2013
I have this code which filters a form with the command button ("Command 82"), but it only searches in the "TASK_NUMBER" field.
How can I make it search multiple fields at once? I feel like you can just use the "Or" operator, but where do I put it?
Private Sub Command82_Click()
Me.Filter = "TASK_NUMBER Like '" & "*" & Me.Text80 & "*'"
Me.FilterOn = True
Me.Requery
End Sub
View 9 Replies
View Related
May 9, 2013
I'm trying to implement a macro to filter through accidents to see if my company has already been hired on a particular case.
In the first form the user enters the date, location, and last name of one of the drivers. Then they click a button which opens a new form as a datasheet with accidents that match ANY of the three controls. Evidently, I need a filter that contains some form of a series of OR operators.
However, whenever I try any variation of a button that opens the second form and then applies the filter, I always get no matches.
View 12 Replies
View Related
May 3, 2012
I have created a combo box search for my form based on three categories, 'Student Name', 'Nationality', 'Age' using the wizard. When I click on my combo box in form view, I see 'Alex', 'UK', '19' and 'Stephen', 'Sweden', '22' in the dropdown list, but I do not see the headings 'Student Name', 'Nationality', 'Age' as the first item on the list.
View 1 Replies
View Related
Dec 10, 2014
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
View 1 Replies
View Related
Aug 21, 2013
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
View 6 Replies
View Related
Sep 21, 2004
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
Code:
View 5 Replies
View Related
Sep 25, 2014
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
View 2 Replies
View Related
Nov 16, 2006
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Jun 27, 2006
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
View 6 Replies
View Related
Apr 26, 2005
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
View 2 Replies
View Related
Jul 25, 2005
Hi,
I have a form on which I have about 6 combo-boxes and a set of 3 radio buttons.
I'm to the point that all the querys that fill these combo-boxes are in place.
What I want now is a search button that updates a datagrid under it when clicked. The query in this datagrid needs to be parameterised with the selected values in the comboboxes or radio buttons.
It should be possible to only select one combobox before pressing update.
At this point I placed a subform in the form to bind this query to. ( the datagrid I need).
Is a subform the correct object for this or are there other possibilities?
For some boxes only a line to the where clausule of the SQL statement needs to be added. For some other (one) and the radio buttons a join needs to be made with another table.
So I will have to build my sql statement dynamicaly in some vba code, run it when the search button is clicked and refresh the datagrid.
Does ab has a sample database in which such a search form is being used?
I must have downloaded like 10 sample DB's now but nothing I can use...
all help or advise greetly appreciated.
View 3 Replies
View Related
