Queries :: Filter From Combo Box Also Has IIF Statement?
Mar 15, 2013
Here is what I have in my Query
IIf([Forms]![Report Form]![Combo56]=0,1 or 3,[Forms]![Report Form]![Combo56])
in the combo box I have it setup like this
Yes = 1
No = 3
All = 0
it is pulling for a SQL database and what I am looking to do is if they want them all select 1 and 3 but when I save the Query is changes it to this and doesn't work.
IIf([Forms]![Report Form]![Combo56]=0,([BigAssQuery MTD].[Calc_ID])=1 Or ([BigAssQuery MTD].[Calc_ID])=3,[Forms]![Report Form]![Combo56])
My form has a combo box which is bound to a query that selects a list of member numbers. The value selected the goes onto filter a list driven by an SQL statement.
Code: Private Function PopulateADO(qpMemberNo As Long) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset
[Code] .....
This works fine when a single member is selected from the combo. But obviously i want a "select all" or * wildcard option.
Question is 2 parts. 1. How do i add the all or wildcard option to the bound combo? 2. How do i code the all or wildcard option into the SQL statement? Is it something like is not null ?
I have a combo box as a value list and all the values apart from one are valid data entries in the table but I want to combine two of the values and then get the query to run if the combined values are selected. The value list is this:
(All);Brand1;Brand2;Brand1 & Brand2;Brand3;Brand4
The criteria in my query is this:
IIf([Forms]![MyForm]![Brand]='(All)',[MyTable]![Brand],IIf([Forms]![MyForm]![Brand]='Brand1 & Brand2',"Brand1" Or "Brand2",[Forms]![FrmCustomListATM]![cbBrand]))
On my database, I have 2 forms. One is to enter data into a Master table, and the other is to specify search terms for a query, and I'm having a problem with the search form.
I've got 2 combo boxes, a tick box and some text boxes which I use to specify criteria for the query. All of the text boxes and the tick box work fine, however the combo boxes are giving me trouble.
I'm using the critera Like "*" & Form![SearchForm]![Combo/Text/Whatever1] & "*" for the text boxes and tick box and this worked fine. However, no matter what way I try to use this, it doesn't work for the combo box. I've tried manipulating the Like function as much as possible, with and without the wildcards, the & signs, everything.
What happens is that either they do not filter out results at all and don't affect the query, or they bring up a blank query if I leave the combo box blank.
I want to create a cascading combo box - so far so simple, but in all the tutorials I can find the options in the second combo box are exclusive to the first e.g. combo box 1 selects "state", combo box 2 then displays "city". Each city can only be in one state.
In my scenario each city is in several different states - and so far I'm failing to come up with any sort of strategy as to how to deal with this. I tried giving each state a "yes/no" formatted field in the "city" table, figuring if I could use the first combo box to point access to the right field in the "city" table I could filter those records by "WHERE [fieldnominatedbycombobox1] =True" - but I don't know how to make a one combo box determine which field a subsequent combo box filters by.
I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query
Code: WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));
This works perfectly for cboclient1 problem comes when I try and add cboclient2
I have tried
Code: WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/
I would like to filter a data from a table using a combo box in a form.
The field I would like to filter is called ManufacturersID in MainTable and consist of text and wildcard characters [e.g., Teledyne O&G (ODI), TE Connectivity (Deutsch), etc...], so are the combo strings which are extracted from the table ManufacturersTable. It is not filtering anything. I suspect that the wildcards and spaces are the problem. I also tried to get the combo string to a text box in the form and use it in the query with no success. Additionally, I also tried Like [Forms]![FormName]![textbox] in query criteria but still not filtering...
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code: SELECT SupplierID, SupplierName FROM tblSupplier ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code: In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a bound form, in its heading I have a combo box which lists three choices. Basically I want to filter all my purchase records by checking a field PUOrderNb (Example: PO200100025) against the choice made in the combo box, namely DE (for demand), PO (for Purchase order) etc. On the after update event of the combo box, I have the following code:
Dim strfilter As String strfilter = " left ([PuOrderNb],2) like " & cboFilter.Column(1) Me.Filter = strfilter Me.FilterOn = True
When I make a choice in the combo box, I get a window asking me to enter a parameter value and it lists the value of the combo box choice as a sort of a title just above the white input fame.When I type in PO for instance, the program does correctly filter all order numbers starting with PO, but the whole point of having a combo box is not to have to type anything.The other odd thing is, when I change the choice in the combo box, after my first choice, I do not get this parameter question but nothing happens as to filtering. The first choice remains active.
I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp
Exercise 1
For this exercise, I have the following tables:
tblClients containing client names tblProjects containing some project details
I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form. Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.
Exercise 2
I have the following tables:
tblProjects as above tblWorkstream containiig names of workstreams and some other details.
Every project has one or more workstreams
I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.
Please let me know if you need me to explain any part of this better.
Thanks in advance.
ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I'm having a little difficulty setting up a combo box to filter another combo box. I've actually got one working but the second one is giving me all sorts of errors. On the attached database, there's a form called frmAddNewRecord. At the top of the form there's a combo box which allows you to select a name and another combo box beside it which acts as a filter so only names from a certain section are shown (working fine).
The subform which is attached to this form and contains the training details, has another combo box which allows you to select a job...I've tried to add a combo box beside this to filter it but I can't get it to work.
I have a query, called Building Query. it has 5 columns.
Building EquipmentLocation PartName Category PartNumber
I have created a form, with these five as comboboxes. I want each to reference the one before it, so the user can select the part without error. This is somewhat complicated because each building has multiple locations, which in turn have multiple parts, which can be in mutiple categories, etc............ However, althought the selection process works it's not accurate. example: say I select building 1. and location 1.2. The part name should be limited to "belt" and the category limited to "ef", thus limiting the part number to 5vx630. This is what happens instead: I choose building 1, and then location 1.2. The part name is limited to "belt". The category gives me a full list of all four categories available in building 1. So then I choose the category "ef" (because I know what to choose) and am given the full list of all "EF" part numbers. So It seems to work fine until the last two selections. Perhaps you can help.
Name: cboBuilding RowSource: SELECT Building.BuildingList FROM Building; after update event procedure: Private Sub cboBuilding_AfterUpdate() Me!cboLocation.Requery End Sub
Name: cboLocation Rowsource: SELECT [Building Query].EquipmentLocation FROM [Building Query] Where BuildingList = cboBuilding AfterUpdate Event Procedure: Private Sub cboLocation_AfterUpdate() Me!cboPartName.Requery End Sub
Name: cboPartName RowSource: SELECT [Building Query].[PartName] FROM [Building Query] Where EquipmentLocation = cboLocation AfterUpdate Event Procedure: Private Sub cboPartName_AfterUpdate() Me!cboCategory.Requery End Sub
Name: cboCategory RowSource: SELECT [Building Query].[Categories_CategoryName] FROM [Building Query] Where PartName = cboPartName AfterUpdateEventProcedure: Private Sub cboCategory_AfterUpdate() Me!cboPartNumber.Requery End Sub
Name: cboPartNumber RowSource: SELECT [Building Query].PartNumber FROM [Building Query] Where Categories_CategoryName = cboCategory AfterUpdateEventProcedure: Private Sub cboPartNumber_AfterUpdate() Me!Productssubform.Requery End Sub (this is to requery the subform the then displays the part number, the number in stock, and the number needed to be at full changeout level) This also isn't working all of a sudden
Can anyone help me???? It must be simple but I've read every thread in here about combo boxes and SQL's and just can't figure it out myself.
I'm trying to get an SQL statement to take a value from a combo box in a WHERE LIKE clause.
For example:
INSERT INTO tblInspectionTempp (BuildingID, DoorNumber) SELECT tblDoorData.BuildingID, tblDoorData.DoorNumber FROM tblDoorData WHERE tblDoorData.BuildingID LIKE = '[Forms]![fmInspectionColumns]![cmboBuildingID].Value'"
The errors I'm receiving are either Missing Match or incorrect Syntax, depending on my trial and errors methods regarding the WHERE clause.
I have a form with textboxes and comboboxes and a Button,when button is clicked, a new query is made, with criteria based on boxes on form.For example this one :
Quote: If Not IsNull(Me.cmbType) Then Where = Where & " AND [TypeID] = " & Me.cmbType
What would be the statement for a range between to combo boxes.I have 2 comboboxes (cmbBox1 and cmbbox2), show only records equal and between the ones selected in cmbBox1 and 2.
I have a form that has a combobox with table names in it. I want to execute a very complicated SQL statement but I want to put the table name (from the combobox) into the SQL statement. I'm planning on making a button to do this, but I don't know how to put the SQL statement in VBS which would use the combobox value as the table name in the SQL statement.
Hi All, Just after a code or way to filter client details via a combo box. Basically select combox drop down menu select the sales rep then it filters by sales rep and shows only the clients for that sales rep. I can get it to filter but it does the first client of that sales rep but not the rest.
I have two combo boxes, Company Name & Name. The data that is selected is used to run a query from a button. However i would like to set it that if the user (but not required) selects the company name then only the names of the people belonging to that company appear, but if no company is selected then all the names for every company appear.
Can anyone make a suggestion as to the best way to go about this.
I've read several threads about one combo filtering a second combo on a form, but can't seem to find a similar enough situation. Can you please advise on the following:
I have a form based on a query containing a whole load of fields from several tables. In particular the query contains a field called WorkstreamID from tblWorkstream.
Each tblWorkstream record has a Workstream ID, Workstream and foreign key called Project ID from tblProjects.
I want my form to have two combos: one for Projects (unbound) which filters the combo for Workstream (bound to the underlying query of the form)
I have a form with a list box that is filtered by the entry made into a combo box. It works fine except that I want an option which shows all records in the list box. I tried using the formula below in the query criteria for the list box but it returns no records when I select All in the combo box. Why won't it work? Is there a better way to do this? Can anyone help?
hi all, another quick question (last one for awhile I promise) but is there a way to add an option to a combo box (Filter by Selection) and Remove/Filter sort without right clicking on it? cheers, Andrew
