Queries :: Filter Query From Combo Box
Nov 18, 2013
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.
View Replies
ADVERTISEMENT
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])
View 2 Replies
View Related
Mar 16, 2013
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.
View 13 Replies
View Related
Jan 31, 2014
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));[/
This doesn't work at all....
View 2 Replies
View Related
Feb 19, 2015
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...
How can I overcome this?
View 2 Replies
View Related
Jul 13, 2015
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.
View 7 Replies
View Related
Jun 15, 2013
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!
How would I achieve this?
View 4 Replies
View Related
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Jul 25, 2005
Hello All,
Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.
I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.
ComboBoxes include; Year. Movie. Country.
What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.
I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.
' Code to change the movie information, when they select the combo box
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.
View 3 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
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Feb 19, 2015
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
View 3 Replies
View Related
Jan 23, 2014
I'm trying to filter an append query by a year selected in a combo box [cboYear] for a field [ProgramDate]. The AfterUpdate on the cbo filters the append query based off of the selection (or selections-I'm using multiple combo boxes on the form). I then run a report based off of the appended table for a report.
I set my criteria for [ProgramDate] the query to be:
Code:
Year([ProgramDate])=[Forms]![frm_rpt_Programs]![cboYear]
The above code isn't working, even when I just try to run the query while the form is open. It's still returning all records.
View 2 Replies
View Related
Feb 17, 2014
I have a query that I am using in VBA to select the number of days equipment is past due for service. I have an equipment table with a last maintenance field (tbleqDP with field DPLstMnt) and another table (tblmaintdue) that defines the maintenance interval. I have a query (qrynumdays) that finds the number of days from tblmaintdue for the equipment type based on a value in cboMaintEquipType combobox. The VBA then generates a report based on which type of equipment is selected using a select case statement. An excerpt of the VBA is below:
Select Case cboMaintEquipType.Value
Case "DP"
Set qdf = db.CreateQueryDef("qryoverdue", _
"Select DPTag, DPLstMnt, DateDiff(""d"",DPLstMnt,Now())-NumDays AS Overdue from tbleqDP, qrynumdays where Overdue > 0")
DoCmd.OpenQuery "qryoverdue"
DoCmd.OpenReport "rptDPmntovrdue", acViewPreview
I am having problems with the "where overdue > 0" statement. It is asking me for parameter value for overdue. How can I get this to automatically filter out any values that are negative - in other words, don't report on equipment that is not past its maintenance interval due date.
Also, I think there is a way around how I am naming my query in the createquerydef function because later I just delete the query after the report is run. Is there a better way to do that.
View 2 Replies
View Related
Apr 17, 2014
I have a table like this
Cultureworkload
ReasonfortestingIRLNumberDiagnostic sputum specimens processedFollow up specimens processedReception
Follow up7/20130102-Jan-13
Diagnosis8/20131002-Jan-13
Follow up9/20131002-Jan-13
Diagnosis10/20131002-feb-13
Follow up11/20131003-feb-13
Diagnosis14/20131003-Mar-13
Diagnosis15/20131003-Apr-13
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber
SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber]
FROM Cultureworkload
GROUP BY Cultureworkload.Reasonfortesting
PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
View 3 Replies
View Related
Nov 13, 2014
So I have the following query field which calculates another field.
How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].
Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")
View 8 Replies
View Related
Nov 21, 2014
I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).
How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.
View 1 Replies
View Related
Jul 27, 2013
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start
PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime;
TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM
SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK
WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy")))
GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block
PIVOT PRODUCTION.ACTIVITY;
//Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
View 1 Replies
View Related
Jul 17, 2014
I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).
View 14 Replies
View Related
Nov 8, 2004
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!!!
Thanks
View 14 Replies
View Related
Nov 5, 2013
i have a table with three column Named
1-State
2-City
3-Customer
on a form i m placing 3 combo box for each column how can i filter combo 2 from table after select value from combo 1
View 1 Replies
View Related
Jul 24, 2014
Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.
This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:
Quote:
SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));
View 14 Replies
View Related
Jul 25, 2013
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
View 5 Replies
View Related
Dec 17, 2014
I was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
View 2 Replies
View Related
May 16, 2013
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies
View Related
Nov 18, 2014
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
View 4 Replies
View Related
