I have a multivalue criteria inside a listbox that I need to use to filter several data out of a table by using a query. The multivalue textfield is the 3rd column of the listbox (eg. John Jonson,Tom Boost,Kim Moore). When I select a row inside the listbox, I want all the adressess of the people that are mentioned in the 3rd column when I click on a button.
How do I make this criteria for a query to get the information I wanted?
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule
FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value
WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
Hi guys,
Cant seem to work this one out. I have quite a complex search form. The underlying query displays the results in a list box on the same form.
So far I have used the following expression for all the fields on my form (whether text or integer values):
Like "*" & [Forms]![Frm_FrmSearch]![AssetName] & "*"
This appeared to work correctly. However, now my Asset Management System is storing a number of Equipment Type's. As one of the query criteria is Equipment Type ID it means that selecting PC (1) also displays the details for Printer (11), Scanner (12) etc......
I know why it does this (because these numbers start with a 1 and I am using a like expression). However I cannot seem to get it working.
The equipment type value is present in every record so I dont think I can use =FormValue or FormValue Is Null. I did try:
=[Forms]![Frm_FrmSearch]![EquipmentType] Or
Like "*" & [Forms]![Frm_FrmSearch]![EquipmentType] & "*"
but it seemed to skip the first parameter and still displayed printers etc. as before.
Any ideas?
How can I search a department field by the start of the data in it...
For example, I have departments Purchasing, and Purchase..i want to search "Pur" and get both.
**but, i dont want to get any other matches with the letter "pur" unless they start the field.
Hello,
I have a feeling this may be easier than I expect however I am at a standstill.
I have a Query that is called from an unbound list box when data is typed into one or all three unbound txtBoxes "txtLastName" ,"txtFirstName", and "txtVIN" the query populates the listbox almost as it should..
The purpose is to identify duplicate entries based on three critera, last name, first name and VIN with the VIN bieng an execption meaning that if the VIN does not match I still want the matched first and last names to remain in view..
When I open the form where the list and text boxes are all records show in the listbox and as I begin to type the last name all records that do not match that critera are dropped, the same goes for the first name this works great. Once I get to the VIN however if there is no match I loose all three and the listbox is empty.
Is there a way to maintain matched names in the list view eventhough there is no match for the VIN?
Below is the code I am using in the Query Design, it is the same for all three fields Lastname, FirstName and VIN.
Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"
Thanks!
Fen How
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
Looking to be pointed in the corect direction,
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed???
The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box.
also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
many thanks
Cheers
watson
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.
I have a search form that uses several comboboxes, textboxes and checkboxes that are used as criteria in a query. You enter in the relevant information, hit search and a report opens based on the filtered query.
What I would like to do is change one of these combo boxes to a listbox and use the multiple selection as the query criteria. I know it's not as simple as just putting the listbox as a criteria in the query, and I've also tried many different variations on the varItem and strWhere code.
I would like to have the user select one or more values in a list box, and use those values as criteria in a query. Then the query will be used as a source for list boxes in another form based on the values of the previous selection.
Example:
User selects options 1, 3, and 4 from the list box on Form A. The query then sets the criteria to anything related to 1 OR 3 OR 4. Then in another form, Form B, all the data tied to option 1 is in its own list box, all the data tied to option 3 is in its own list box, and all data tied to option 4 is in its own list box.
I have a multivalue field containing values of A,B,C and D with primary IDs of 1,2,3 and 4 in that order.
If I was to run:
Code:
UPDATE Table
Set Table.Multivalue.Value = 1
WHERE Field 1 = "True" AND Field 2 = "True";
Then run
Code:
UPDATE Table
Set Table.Multivalue.Value = 2
WHERE Field 1 = "True" AND Field 2 = "True";
Will I have a Multivalue field as A,B or just B? I would like it to be A,B but I have my doubts as to whether this would work.
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 RelatedI 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.
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3
EmployeeName John
EmployeePhone 555999555
EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3
ClientName ABC Company , XYZ Company, PQR Company
fkeyLocationID
tblLocations
LocationID 1 , 2
Location New York , Chicago
tblEmployeeClients (junction table)
fkeyID 3
EmployeeClients (multivalued number) 1,2
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported
John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
1. 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 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 RelatedHi,
I have a search query (query by form) which is picking up keywords from a form and displaying matching results.
I want to add a tick box to the form, and if this tick box is ticked, I want the search to only include results which have a certain field NOT blank.
ie.
frmSearch will have tick box named "Website"
If "Website" is ticked on the form and the QBF run, the query will only show those records which have information in the field "Website Address" in the table tblPublication.
If the "Website" tick box it NOT ticked, the query will show all records, regardless of having information in the field "website address" or not.
can i do this in the criteria of the query?
I don't really want to do it by having a seperate query which is run by a seperate "Search" button on the form - this would be possible by having a 2nd search button (titled "Search for results with website") run a different query which has the critera that the field "Website Address" is not null.
I dont really want to have a seperate button and query as it makes it a bit messy - would rather the one query look up if the tick box, and if "ticked" then display only those with content in "website address" field, and if not ticked, display all regardless of content of field "website address".
any ideas?!
Cheers
amx
I have one table with 4 fields
TYPE
CODE
REASON DESCRIPTION
SHORT DESR
When I try to do a query to search on CODE it returns nothing. I don't understand what I'm doing wrong. Would someone please look at this DB and help> Thanks
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?
Hi
I have the date on my table as 01/02/2006, there are others like this, i'm wondering how i can search for the whole month, something like **/02/2006. i have tried that way and didn;t work.
Any ideas??
Thanks
Hi All,
I need to make a search criteria within the same field,
for example here 'Demo' should selected from 'xxx' to 'xxx' number.
Thanks for reading, any suggestion would be appreciated
good day :-)
Sorry if this is an easy question, but I've been racking my brain trying to get this one.
I have a reference table of sales agents and assigned territories. Ex -
Agent Territory
Tom Smith IN, MI, TN, AR
Deb Jones IA, KS, NE, MO
Now I want to assign agents to a list of prospects based on their state id. However, I can't just join the state field from the prospects table to this reference table. How can I get this to work? Any help is greatly appreciated.
I have a form with drop down boxes that list 3 related fields and I have a search button that will requery based on the the input from these boxes. These boxes are all prepopulated with the data and I want to be able to select something from Box1 and then based off Box1 change whats populated in Box2 and Box3. Any idea's???
I already have a query setup like this to requery a query I make:
( I have a button that initiates the requery based off what input is given)
Box1:
IIf(IsNull([Forms]![FormName]![Combo1]),[Field1],[Forms]![FormName]![Combo1])
Box2:
IIf(IsNull([Forms]![FormName]![Combo2]),[Field2],[Forms]![FormName]![Combo2])
Box3:
IIf(IsNull([Forms]![FormName]![Combo3]),[Field3],[Forms]![FormName]![Combo3])
Problem with this is that is does not requery correctly and it only filters on one of the criteria ( Field1) and spits out all records for the other two?
So I figured since I already populate the drop downs with the records why not just change the contents of the drop downs?
If anyone can give me some insight it would be much appreciated?
Thanks in advance.
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.
Hello,
I have been trying to produce a front end for a multi criteria search. I have used one of the sample databases from the site and amended the code as necessary, but obviously not correctly. I can't get it to show me the records based on my search criteria.
I would be grateful if somebody could have a look and let me know what I've done wrong (cut down DB attached). If I can crack this I want to do another multicriteria search for other parameters.
One other question - is it possible to take those filtered records and dump them into a report? For example, say I select one parameter and want tpo print all records associated with that parameter?
Thanks
I am using the below code to open a form from a search form. This code works well because I could leave a search field blank, and the code would treat the blank search fields as a wild card search. Here is the problem; I want to be able to search a range of ages in addition to lastname and first name. I added two fields (“AgeStart”, “AgeEnd”) in the search form and added ([age]>= '" & Me.AgeStart & "*'and and [age]<= '" & Me.AgeEnd & "*'") to the end of the stLinkCriteria. This addition works well if there is an age range is entered into the search fields. If nothing is entered into the age range fields of the search form, access does not treat the empty age range fields as wild card like the other fields. I would like Access to treat the empty age range fields as a wild cards search. Is this possible, and if so, how would I go about doing this? Any help on this would be greatly appreciated.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Personnel_frm"
stLinkCriteria = "[last]like '" & Me.lastname & "*' and [first]like '" & Me.firstname & "*'"
DoCmd.OpenForm stDocName, acNormal, stLinkCriteria