[Query] Help With Criteria To Filter Certain Data
Feb 1, 2008
Hi there,
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number
1 A1 fjlafs 453453454
3 A2 fsdfsd 343534534
4 A3 gsdgsdg 354543534
5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
Thanks in advance!
View Replies
ADVERTISEMENT
Mar 17, 2005
I have set up a query that will pull data from table1. There are two fields in my query to which I will filter by entering certain basic criteria. In the criteria line of my query field, I have entered "800" to only return this type of data. If I run the query, it returns only those fields, which is exactly what I want. However, I also need an additional filter in another field. I have entered "4", to return only those data matches.
So, my entire query is based on returning only the data from table1 that matches the two criteria ("800" and "4"). Problem is the query will not return any data when I enter the "4". Any ideas why this would happen? There does appear to be an extra space in table 1 for the field containing "4". I have tried to set the criteria to match, but it still does not return any values.
Any advice greatly appreciated.
Thanks,
Todd
View 4 Replies
View Related
Aug 8, 2006
HI there,
This sounds simple, but I'm not sure what the coding is in SQL. Perhaps someone can help..
I have a query which has criteria set by a form.
The First sorts the data in the query by SalesPerson from the selection made in a Forms combo box.
The second sets the date the query filter starts at and finishes.
So far so good.
However, I have an entry in the combo box for "ALL" sales people so that the user can see all the results for all sales people.
SELECT tblSalesinfo.Sales_reference_autoID, tblSalesinfo.CustomerID, tblSalesinfo.Completed, tblSalesinfo.[Sales CO], tblSalesinfo.Date, tblSalesinfo.Cust_Ref, tblSalesinfo.Customer_Name, tblSalesinfo.Description, tblSalesinfo.CTN, tblSalesinfo.Sold_£_Phone, tblSalesinfo.Sold_£_Accessories, tblSalesinfo.Sold_£_Other, tblSalesinfo.Labour_£_Charge, tblSalesinfo.Total_Sales, tblSalesinfo.Invoice_Number, tblSalesinfo.Delivery_Note, tblSalesinfo.Tariff, tblSalesinfo.Commission_due_amount, tblSalesinfo.Other_value, tblSalesinfo.Total_deal_Value, tblSalesinfo.Cost_A, tblSalesinfo.Cost_B, tblSalesinfo.Cost_C, tblSalesinfo.Cost_D, tblSalesinfo.Total_Cost, tblSalesinfo.Total_Deal_Balance, tblSalesinfo.[Margin%], tblSalesinfo.Sales_Commission_Due, tblSalesinfo.[Recovered Stock]
FROM tblSalespersons INNER JOIN tblSalesinfo ON tblSalespersons.SalesID = tblSalesinfo.[Sales CO]
WHERE (((tblSalesinfo.[Sales CO])=[Forms]![frmDisplayResults]![SalesCO]) AND ((tblSalesinfo.Date) Between [Forms]![frmDisplayResults]![StartDate] And [Forms]![frmDisplayResults]![EndDate]));
Can someone help with the code to say that if [SalesCO] = "ALL" it does not filter the results by Salesperson and shows the query for the date range only.
Many thanks!
View 2 Replies
View Related
Jul 31, 2007
Hi all,
I am designing a database that is completely input driven. This is my first database so because of the reliance on the user to populate everything it has been rather confusing.
I have a query that I have set up that uses 3 selections, 2 from a combo box, and one from a text box to populate 3 distinct fields. In this case its a Plant #, a Material #, and a Version # (version corresponds to another report that this tool will serve as an input towards). After these are populated, the user will be manually inputing information for 24 versions after the current version.
My question is how can I go about referencing what I have in the Version # on the query, or the combo box for that matter?? I need to reference the current version selected than have it display only versions 2 years out.
fyi - I have a table with 5 years worth of versions(technically months) in my main table. Could I reference those?
Also, would it be easier to include every single version that I already have stored and just filter for the 2 years out??
Thanks for the help
View 1 Replies
View Related
Apr 21, 2008
Below is the SQL I have on a Union Query. Each Query is
based on a date range. The first Query date is Planned
Immplementation Date. The second Query date is Revised
Planned Implementation Date. If the Date in the Revised
Planned Implementation Date is higher than the EndDateTxt
Date Range I do not want the results to appear for that
ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number]
UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
View 5 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
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
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
Nov 14, 2013
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
View 6 Replies
View Related
May 10, 2014
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
View 9 Replies
View Related
Jan 26, 2015
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
View 5 Replies
View Related
May 10, 2014
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
View 3 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Aug 14, 2012
I have large dataset(more then 15,00,000 rows) In one column I have Amount stored as text eg: $15,000.00 $ 12,000.00 $ 17,520.00
Data is imported from various small files and is updated regularly. Now I need to filter dataset based on this column criteria like > 15000, <= 5000
How can I configure query to filter data.For example data are:
Id
Detail
Value
Part
25252
Some text here
$17,294.00
1
[Code]...
View 14 Replies
View Related
Mar 14, 2006
I have a form which is linked to a query. The main form which calls this form sets the filter for the query. This works fine and I can navigate all the filtered records.
The problem is when I go to add a new record the filter information goes away.
For backround this is DB for project information. The user clicks a button to view notes on the project. The notes are stored in a different table and the project_id is the common field which links the two together. I want this to function where the user can open the subform read the notes and add a new one if needed.
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 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
Sep 14, 2007
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
Thanks in advance,
Emily
View 5 Replies
View Related
Nov 5, 2007
Hi
I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
Thanks Marcus
View 3 Replies
View Related
Nov 4, 2006
Query1:
Src: Table1 joined Table2
ID (Type Text)
Title (Type Text)
Remarks(Type Text)
Formatted: FormatTitle([title],[Remarks])
Expr1: InStrRev([Formatted], "~")
public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String
'do process code here very complicated an long, but works find in the end
'creates a Multi-String delimited by | (pipe)
end function
The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.
However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria
Query2:
Src: Query1
Title (Type Text)
Remarks (Type Text)
Formatted(Type Text)
Exr1 (Type Number) criteria >0
This Query Also produces the Data Type Mismatch in Query Criteria
pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused
View 2 Replies
View Related
Feb 6, 2005
I have a Form> [Management]
I have a SubForm> [Management_History]
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
View 2 Replies
View Related
Nov 5, 2014
I have a data base with 6 tables
stock
stocklocation
location
orderdetail
orders
Customers
i have created a query to do the calculation of how much stock came in went out and is still on hand .the problem im having is that it shows me the 0 total for product with no stock in it when i put my criteria as >0 in my goods on hand field it show me no data in the query.
my calculation fields is as follows GoodsOnHand:[GoodsIn]-[GoodsOut]
Goods in gets calculated as follows GoodsIn:Nz([SumOfGoodsIn],0)
Goods out get calculated same way as goods in.
The Reason i have a sum of goods in is because i have to calculate the quantity in another query because it is spread over lots of locations.
View 1 Replies
View Related
Mar 19, 2012
in a query, I would like to capture data that is in the following format:
123456 (6 digits) AND
A12345 (1 letter and 5 digits) AND
AB1234 (2 letts and 5 digits)
The letter could only be at the beginning of the abbreviation.
View 1 Replies
View Related
Sep 21, 2005
I have a form that is opened filtered based on the selection of a ComboBox based on the following code.
Now I want to futher restrict the data that is shown when the form opens by showing;
a) only those records where [DelDate] is null, and
b) only those records where [Deldate] is null or [Deldate] > date()-2
I know it has to be incorpoprated into stLinkCriteria I'm just not sure on the syntax and structure
Thanks in advance and sorry for the simple question :o
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRM_AWMPonum"
stLinkCriteria = "[ClientID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command3_Click:
Exit Sub
Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub
View 2 Replies
View Related
