Using A Combo Box Selection For Query Criteria
Nov 1, 2006
I have a report that is based on a query. In the query, I have a field called "Mode". In the criteria section for the Mode field, I am calling the selection from a combo box on a form called "frm_main". So the criteria for the Mode field is "[Forms]![frm_Main]![Mode_ComboBox]". This combo box has selections for 1,2,3,4,5. I want a selecton on the combo box that will work with the query to show all modes. Sometimes something weird gets entered into the database like "NoMode" for example. I have tried adding a combo box selection of "*" , "Is Not Null" and "". If I manually type these into the criteria section of the query, it works fine. But when I use these in the combo box and call the combo box selection from the query, it does not work.
Does anyone have any idea what I can use in my combo box selection that when selected, the query (and hense report) will show all data?
Thanks,
Jim
View Replies
ADVERTISEMENT
Oct 17, 2006
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why?
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
Attached is my db. Please help me out here.
View 2 Replies
View Related
Sep 11, 2014
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
View 2 Replies
View Related
Jun 8, 2007
Hi, I have been reading through the forum and found alot of really interesting stuff but cannot seem to find the answer to my specific problem. I'm sure it's easy but i'm sick of trying to figure it out now.
I am trying to build a form to display a shift rota. My tables contain daily shift patterns for 6 staff members for 2007. I want to build a form that has a subform displaying the rota for a particular analyst and a control in the master form to select which of the 6 staff members you wish to view. I have built a master form with a combo box. The combo box holds the names of the six staff and gets this data with a select statement in the Control Source field from the employee table. I constructed this with the wizard. I have constructed a query to drive the sub form. The query builds the rota, getting data from my tables but gives all shifts for all dates for all staff . I want to restict this in my subform to all shifts for all dates but for 1 member of staff. The member of staff should be defined by the selection made in the combo box in the master form. I then have a statement in the criteria of the query driving the subform to restrict the results by name to the name selected in the master form combo box. I'm sure this should work as i have used similar methods in the past. The statement in the query criteria reads like this:
[forms]![frmRota].[cmbAnalystSelect]
this is in the field of analystName, so the criteria should find all instances where the value in the analystName field matches the value in the cmbAnalystSelect control. However, the query always returns no results.
When i run the quey i have already opened the form (frmRota) and selected a value for cmbAnalystSelect, so the quey should collect the value from the combo box and filter the result according to the criteria but it seems to filter out all the results. If i run the query without first opening the frmRota form then i am prompted by a dialog box to enter a value for [forms]![frmRota].[cmbAnalystSelect]. If i enter a corresponding correct value (a valid name for a member of staff) the query returns a correct set of results, i.e. all shift patterns for all dates for the selected member of staff. When i have the form already opened i am not prompted for a value by the dialog box which would suggest it is at least recognising the form is open and that the control is there.
The last thing is that my ComboBox control is not bound to anything, when i view the control in design view it says it is unbound. I have nothing in the control source for the ComboBox. In the row source i have a select statement to pick out the employee names from the employee table, this then poulates the list in the ComboBox.
I hope this is enough info for someone to understand my problem and offer some advice, i can elaborate, attach screen shots etc. if needed.
Thanks in advance for any advice you can offer.
View 4 Replies
View Related
Nov 25, 2013
I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.
The query itself has criteria that looks at the open form and selects the correct record.
My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.
The selection criteria in the query is:
Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]
Basically I want to override the resulting data with another record when I run an event on the form.
View 3 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Jan 9, 2014
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm using 2003 and have some VBA ability.
View 5 Replies
View Related
Feb 13, 2006
I'm new at Access 03. I have a combobox that is sourced by a table, Rowsource property is:
SELECT Classes.TableID, Classes.ClassID FROM Classes;
The name of my combobox is "ClassCombo".
I'm trying to generate a report based on my combobox selection, but I can't reference the value of the selection. The query tied to my report has the following SQL:
SELECT ClassesForInstructors.ClassID, Instructors.InstructorID, Instructors.FirstName, Instructors.LastName
FROM Instructors INNER JOIN ClassesForInstructors ON Instructors.InstructorID = ClassesForInstructors.InstructorID
WHERE (((ClassesForInstructors.ClassID)="[Forms]![School]![ClassCombo]"));
I'm trying to grab the "ClassCombo" value as input to my query, but it's not working. What am I doing wrong?
Thanks!
View 2 Replies
View Related
Dec 10, 2007
I have a query that runs info selected from safety visits. I have made a form with an unbound combobox that selects the supervisors name and after the name is selected, I have a button to run the query. When a name is selected, the query comes up blank. When I do not select the name, the query is populated.
My criteria on the query is [forms]![personnel]![combo9]
Combo9 is the unbound dropdown menu with the supervisors name that is taken from a table of names.
Any help?
Thanks in advance.:(
View 1 Replies
View Related
Nov 26, 2004
I have 2 questions which are linked. I am trying to run a query based on a selection from a combo box. Would someone be so kind to indicte if i need to use VB or can i do it directly from Access?
I have made the query and the combo box table but i am unable to get the combo box selection to be used in the query.
Additionally i want the query to look at a date range. In the query criteria i use "=now()" but i want the combo box selection to populate the rest of the criteria so it look like =now()+ XXX where XXX = a number of days, eg 14 or 30.
Any help would be gratefully received.
View 2 Replies
View Related
Dec 1, 2004
I have a form on which i make a selection. This selection then populates a query which in turn generates a report. The problem that i have is that when i go back into my form and make another selection the query, and therefore the report, holds the original data.
My question is how can i refresh the selection and therefore produce different reports based on this selection without exiting the application?
Many thanks for any help provided in advance.
View 4 Replies
View Related
Feb 18, 2015
I have created form with multiple tables field in some of these fields I have assigned the combo box selection the total combo boxes in form is four and all are unique numbers but this form is incomplete.
1.When I select or update any of four combo box then remaining combo boxes and fields on form should be updated automatically with related records.
2.I need to bring calculated fields from multiple queries on to my current form .how I can insert query fields in to form...
View 1 Replies
View Related
Jul 31, 2014
I'm trying to write a query to allow the user to search through records.
To make the interface simple I just want a combobox to select the search field and a text box to enter the search string.
My problem is trying to build a query where the value of a combobox on a form is used as a field in the query.
View 3 Replies
View Related
Jun 25, 2013
I am currently working on an existing database (not created by me) which contains several queries. Each query relates to a particular product.
I am wanting to create a form which has a combo box so that a user can choose a particular product from the combox options and then click on the command button to run the correct query.
How to do this. I have created the form and the combox (together with the list of products). I just need to know what Event Procedure code I need to enter to programme it to look for the correct query and then run it.
For example.
I have the following queries
product1query
product2query
product3query
I have the following options in the combo box
product1
product2
product3
How do I get the command button to look at the product1 option in the combo box and then find and run the product1query.
View 9 Replies
View Related
Jun 3, 2015
I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.
I have another query that will only show the line item selected from the list box. This means the query will only have one line item.
I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.
How can I accomplish this? Also, how can I make a button that deletes the selected line item?
View 3 Replies
View Related
Sep 14, 2006
Is it possible to use a drop down combo box to specify criteria?
I have a query which asks to select a name ( Criteria [Enter Name] ). What I want is to be able to select from a list of names available.
I create my queries using the Wizards, so specific SQL to enable the above would be appreciated if there is not a straight forward solution.
Thanks
Richard
View 3 Replies
View Related
Jul 13, 2015
I have two combo boxes.
My first combo box has the options:
Calendar
Off Calendar
If "Calendar" is selected, I would like to click a button that runs query1 with this criteria: "Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"
Example:
If "Off Calendar" is selected, I would like to click the same button that runs query1 with this criteria: "Not Like "*" & "12/31" & "*" AND "*" & Forms!MyForm!cbo2"
Example:
I'm having trouble setting this up...
View 14 Replies
View Related
Oct 20, 2005
Hi everyone,
I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g
cboContinent cboCountry
Africa Zambia
Africa Congo
Africa South africa
Europe England
Europe Holland
If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland
Thanks
Humphrey
View 1 Replies
View Related
Jul 11, 2012
I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.
What I want to do:
1. I want users to be able to select a category in combo box #1 (example: "Bag")
2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool")
3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"
I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.
View 1 Replies
View Related
Dec 21, 2005
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
View 1 Replies
View Related
May 17, 2007
Hi all
I am trying to get my head around some of the more advanced “features” of access..
At the minute I am trying to change the criteria of a query from selecting a value from within a combo box.. I believe I have got the syntax correct, but I keep getting no results returned..
I am using:
[forms]![frmMovies]![combo2] as the criteria where frmMovies is the name of the form, and combo2 the name of the combo box.. I have set combo2’s source as the table containing the movie information “tblMovies”
Before you ask I am not trying to setup my own movie store, more apply the theory to a bigger DB that I am working on :-p
Hopefully you can help, as I have now been scouring the forums for 2 days and am getting no where..
Thanks in advance
Noel
View 2 Replies
View Related
Jan 28, 2008
Hi All,
I am currently having problems with a multiple criteria query with combo boxes (see attached file).
The problem:
- When user defines all 3 criteria (e.g. selects Project: A, Category: Services and Equipment Type: Packages), the query returns a result - no problem here.
- When user defines no fields (i.e. all combo boxes empty), query returns all records - again, not a probelm.
- However, say a user wants to display all records belonging to just Project: A, for example, the query returns no records. Obviously, there a a number of combinations of this (another example would be a user wanting to show records from project: B, with Category: Equipment Type).
I have included the Is Null statement in the query but to no avail. Could anyone advise on a solution based on the attached database. Your help would be greatly appreciated!
Kind regards,
Stephen
View 2 Replies
View Related
Mar 16, 2006
I have an unbound Combo Box in a form that I would like to use to set criteria in the query. How do I do this?
View 1 Replies
View Related
Mar 16, 2006
I have an unbound Combo Box in a form that I would like to use to set criteria in the query. How do I do this?
View 2 Replies
View Related
Oct 25, 2006
Simple question but I've been stuck for a looong time.
So what I want to do:
1. enter a value into a text box (Home_Tel) in a form (frmStudentClass)
2. which runs a query (qryNameTel)
3. and return the results to a combo box (Student_Name) on the same form.
The same Home_Tel may have several Student_Name results.
-I have entered
[Forms]![frmStudentClass]![Home Tel]
for the criteria in the query
- However I can't get the results to turn up on the combo box
- I have the following:
Private Sub Home_Tel_AfterUpdate()
' run query
DoCmd.OpenQuery "qryNameTel"
Me.Student_Name.Requery
End Sub
Any ideas?
Or is there an easier way to tackle the problem?
Home_Tel and Student_Name are from the same tables.
I have created a query just for Home_Tel and Student_Name
Thanks everyone!
View 1 Replies
View Related
Mar 20, 2006
I have a two combo boxes that I want to use to help filter a query. The field the combo boxes need to filter is a numeric field with values that range anywhere from 200 to 1.5 million. To make the filtering easier I set the combo box up to several values (RowSource = Value List) that will serve as a range.
It seems that the query is not recognizing the values selected in the combo box. The numbers appear after being selected, but the query is not affected by the selection. However, When I enter a number NOT on the list (like 428) the query recognizes the value.
How can I get the query to recognize the Value List entries on the combo box?
View 1 Replies
View Related