Multiple User Specified Parameters For Search Query?
Mar 30, 2006
Hello
I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.
I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?
For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:
Any help gratefully received!!!
:)
View Replies
ADVERTISEMENT
Apr 11, 2014
I created a Access 2010 database query to allow a user to search a list of orders between 2 dates, and I created a form for them to use for this search.
Then I created a report for the search results to land on. What I want to do is have the 2 dates that the user provided be displayed in the header of the report.
I created a new Text Box and placed it in the header. In place of the "Unbound" filler that was there, I placed
"=[Forms]![frm_DateRange]![txtStart]".
I used the following steps to do this:
Clicked on the Text Box controlPressed the "Property Sheet" buttonSelected the "Data" TabPressed the "..." button next to the "Control Source EntryThe "Expression Builder" came upDouble-clicked on the database name in the "Expression Elements" windowDouble-clicked on "Forms", then double-clicked on "All Forms"Selected the rpt_DateRange" formSelected "txtStart" from the "Expression Categories" window.I did not select anything in the "Expression Values" window.
When I go to "Report View", and see the Text Box I added, but inside it, it just says "#Name?"
View 7 Replies
View Related
Oct 18, 2013
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
View 1 Replies
View Related
Jul 19, 2007
Hi everyone...
I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.
Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status
Only "Email" is mandatory, ID is autonumber, the rest are optional.
I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"
Presently, I am using the similar criteria for all the fields:
Like "*" & [Forms]![Search]![txt_FirstName] & "*"
The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"
If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.
How can I alter the criteria for it do search correctly?
I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""
Thanks,
Gautam
View 7 Replies
View Related
Jul 19, 2007
Hi everyone...
I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.
Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status
Only "Email" is mandatory, ID is autonumber, the rest are optional.
I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"
Presently, I am using the similar criteria for all the fields:
Like "*" & [Forms]![Search]![txt_FirstName] & "*"
The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"
If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.
How can I alter the criteria for it do search correctly?
I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""
Thanks,
Gautam
View 2 Replies
View Related
Aug 27, 2013
I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:
Code:
Dim Db As DAO.Database
Set Db = CurrentDb
Dim QDef As DAO.QueryDef
Dim rst As DAO.Recordset
Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")
[Code] ....
Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/
After this I'm going to assign the recordset to a matrix but that's a different story!
View 10 Replies
View Related
Jul 4, 2013
I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers
NYKU023561
TRLU102356
TCNU123023
This will search my container number field and show a pop up message box saying..
Please note below
NYKU023561 - has been found you cannot use this container
TRLU102356 - has not been found please check internal system
TCNU123023 - has beeen found you cannot use this container
Below is the basic search I had before which was all I needed.. how can I adapt this?
Code:
Private Sub Command256_Click()
If (CntSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Dim CNT As String
[Code]....
View 4 Replies
View Related
Jan 4, 2006
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;
Your help will be greatly appericated.
View 3 Replies
View Related
Oct 31, 2005
I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.
SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));
View 1 Replies
View Related
Nov 23, 2005
I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.
I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.
So they can get "Accounting" and "Admin" both in the records that are returned.
The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;
Thanks!
View 1 Replies
View Related
Jul 3, 2013
I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.
However, it would be useful to be able to see all stages at once, next to one another.
So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.
It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.
View 1 Replies
View Related
Nov 9, 2007
Hi,
I really hope someone will be able to help me with this one as I am sure im just missing something simple.
I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:
Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])
Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:
Field1 And Field2 And Field3 And etc = true
I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.
Thank you
View 13 Replies
View Related
Apr 30, 2014
I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.
I have tried:
1306 or 1307 or 1308
1306, 1307, 1308
"1306 or 1307 or 1308"
I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.
So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?
View 2 Replies
View Related
Mar 27, 2014
How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:
SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory
[Code] ....
I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:
1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner
The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).
I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?
View 4 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
Dec 20, 2006
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
View 2 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
Jun 29, 2006
Hi all
I have a query linked to a report that prints a worksheet specific to a individual work item. This report/query picks up the Work_ID value on a form. I have 2 other forms displaying the same work with different amounts of detail. Rather than create a new report/query to run from each form, I am trying to use the one query/report from each form.
The problem is that I cannot get Access to recognise the Work_ID value from the other forms. I have tried the following:
In the Work_ID criteria field building an SQL statement as below
[Forms]![frmVCRUpdate]![Work_ID] Or [Forms]![frmVCRShort]![Work_ID] Or [Forms]![frmVCRLong]![Work_ID] - This does not work, it keeps asking for the frmVCRUpdate Work_ID value when I try to run the query from the other forms
Adding 2 extra Work_ID Values to the query and on the 2nd and 3rd criteria lines specifying that it look for the Work_ID value from the other forms but I get the error above.
Any suggestions on how I can make this work would be appreciated, I'm not sure what else to do.
Craig
View 2 Replies
View Related
Apr 18, 2013
Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.
I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.
I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.
View 7 Replies
View Related
Mar 4, 2013
I am trying to get a multiple keyword search query working.... the single search is working however when i use the Multiple Keyword search the data sheet does pop up but no matter what i put in i just get a blank table and under ID it says "NEW"
The Keywords Column has a bunch of keywords separated by commas on each row. Sometimes there will be the same keywords on different rows under the keyword column... and i want it to return any rows that have 1 or more of the keywords in it or if it is null just to return them all....... this is the statement I am trying...
SELECT Sea.ID, Sea.Category, Sea.Title, Sea.Author, Sea.Organisation, Sea.Date, Sea.Keywords, Sea.Reference
FROM Sea
WHERE (((InStr([Keywords separated by commas,Blank=All],[Keywords]))>0 Or (InStr([Keywords separated by commas,Blank=All],[Keywords])) Is Null));
View 3 Replies
View Related
Jul 26, 2005
I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction. :confused:
SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));
The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.
Thanks.
-Brian
View 8 Replies
View Related
Mar 30, 2005
I often use forms to select a parameter & date range for a subsequent report. Where I have chosen to use multiple combo boxes (two in this case)to select more than one parameter I have run into a problem. The report opens ok the first time but if I close it (report) and change my selection then the report refuses to open. Should I just requery the two combo boxes, is there something else or can't it be fixed?
View 1 Replies
View Related
Dec 16, 2013
I am having some difficulty putting togather a small database (attached).
Basically I want to be able to search for mutiple words and get the results
There are 3 main columns in the main table
1) Shop
2) Part Number
3) Part Type
Problem 1:I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from.
Problem 2:Once I have made my selection I want to search and run query to show me the results. I know that once multiple items are selected in a combo box they are separated by commas/space, so the search must take that in account.
Other nice to have
1) If Shop 1 is selected then only the parts which were sold from shop 1 to appear in the Part number Box & visavera & same goes for Part type.
2) Ability to do a wildcard search so if I Enter "A" in part number I can see all the resealts
View 12 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
Apr 22, 2008
I'm trying to setup a parameter that has a multiple choice so to speak. Rather then the user being able to enter whatever data they want.. I would like them to have a choice between two items.
Is it possible? if so.. how do I do it?
Thanks!
View 14 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
