Query To Filter By 1 Criteria, Or All.
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 Replies
ADVERTISEMENT
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
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 4 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
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
Apr 12, 2006
Hello guys,
Hope you're all well.
I need some help with a form filter I need the form to filter out those records with a STATUS that is "OVERDUE" and a date that is < date().
I've tried...
Me.Filter = "[Status]='Overdue' AND [deadline] < date()"
Me.FilterOn = True
type mismatch ....
I'm confused how the syntax works
does anyone know how the expression should be written.
Cheers
Paul
:)
View 3 Replies
View Related
Jan 3, 2008
Good morning everyone,
I use this code to filter a subform with multiple criteria.
Private Sub Filter_Click()
Dim strWhere As String
If Not IsNull(Me.Coordinator) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[EmployeeID] = " & Me.Coordinator & ""
End If
If Not IsNull(Me.Customer) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[CustomerID] = " & Me.Customer & ""
End If
If Not IsNull(Me.Supplier) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[SupplierID] = " & Me.Supplier & ""
End If
Me.Track_All_Orders.Form.Filter = strWhere
Me.Track_All_Orders.Form.FilterOn = True
End Sub
This Code works great, however i want to use it to filter my report as well, so I put strWhere as a global var and i wrote this code to generate the report with the same filters criteria as the subform:
Private Sub cmdGenerateReport_Click()
Dim stDocName As String
stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Nothing happen when i click on cmdGenerateReport!! any clue?
Million thanks in Advance,
Best Regards,
View 2 Replies
View Related
Jan 11, 2006
I'm building a filter using comboboxes and I'm having trouble with two of them. One is to filter by records that are completed or by records that are not completed. The other combobox is to filter by records that have comments or by records that have no comments.
Filter7
Row Source Type: Value List
Row Source: Completed;[tblMainData.Status]='Complete';Not Completed;[tblMainData.Status]<>'Complete'
Column Count: 2
Bound Column: 1
Filter8
Row Source Type: Value List
Row Source: Comments;[tblMainData.Comments] Is Not Null;No Comments;[tblMainData.Comments] Is Null
Column Count: 2
Bound Column: 1
The VBA Code is:
==================
If Me(Filter7) <> "" Then
strWhere = strWhere & " " & Me.Filter7.Column(1) & " " & " And "
Debug.Print strWhere
End If
If Me(Filter8) <> "" Then
strWhere = strWhere & Me.Filter8.Column(1) & " And "
Debug.Print strWhere
End If
==================
I'm getting an error on building the strWhere variable. Can I use filter criteria statements in a combobox column?
Thanks,
PC
View 1 Replies
View Related
Jun 10, 2006
Hello,
I would like to have a query where I could select just one customer or, by hitting enter, select all.
For example, [Enter Customer ID or Hit Enter to display all customers]
View 1 Replies
View Related
Jul 5, 2013
what's the problem of the code below?
I want to create a filter with more than 2 criteria
Me.FilterOn = True
Me.Filter = "[YEARS] = " & Ycode & "[years] ="&Ycode& "[months]="&Mcode& "AND[POScode] = " & Pcode
View 9 Replies
View Related
Jan 18, 2015
I have following table, forms and reports in my access database:
Tables:
1- maintbl
Query:
1- mainquery based on maintbl
Forms:
1- Input or mainform based on mainquery
2- unbound form: to Filter report
Report:
1-mainreport based on mainquery
Objective:
I want to filter report on followings:
Name(Text)
Month(Text)
Year (Number)
I have 3 combo boxes on ubound form and want to filter report based on three above mentioned criterias.
View 1 Replies
View Related
Sep 28, 2012
I need to filter a subform based on the values on the mainform.
Criteria are Multiple. The idea is to show the user the records that are already existing in the table.
First two criteria works, stuck with the third one
I.Criteria - Creative(text type)
Private Sub Creative_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'"
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub
[Code] ....
Also, how to split the code into two lines
"Creative ='" & Creative & "' And Act_ID = " & Act_ID
when broken into:
"Creative ='" & Creative & "' _
And Act_ID = " & Act_ID
is automatically saved as:
"Creative ='" & Creative & "'" _
And Act_ID = " & Act_ID " and this generates an error on execution.
View 5 Replies
View Related
May 18, 2013
I have a form bound to a table and I need to display and edit records. I want to narrow down the records by applying two filters; one combobox to select the ClientID and another to select the date. What I have so far is this:
Private Sub CboClientID_AfterUpdate()
Me.Detail.Visible = True
CboDate.RowSource = "Select AppointmentDate " & _
"FROM tblSample " & _
"WHERE ClientID = '" & CboClientID & "' " & _
"ORDER BY AppointmentDate"
End Sub
then in the cboDate afterupdate event I have:
Private Sub CboDate_AfterUpdate()
Me.Filter = "ClientID = '" & Me.CboClientID & "' AND [AppointmentDate] = #" & Me.CboDate & "#"
Me.FilterOn = True
End Sub
This appears to work with some records and not with others and I don't know why?
View 6 Replies
View Related
Feb 28, 2005
I need to open my form with a filter attached that is based on criteria already existing in the subform. I've tried putting the following code in the OnOpen event of my main form :
Code:Dim stDocName As StringDim stLinkCriteria As StringstDocName = "Campaign Form"stLinkCriteria = "[Campaign - Last Contact Status subform].Form![Communication Response] in ("2","4","10","11")"DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormal
Basically I want to open my main form (Campaign Form) and only show records where the "Communication Response" is 2,4,10 or 11(a variable on my subform "Campaign - Last Contact Status subform"). Which in itself is a problem because I can't seem to code all of these variables- it will only accept one.
When I run the above code I am getting asked to enter a parameter value for "Campaign - Last Contact Status". I'm not too sure where this is picking this up from or how to fix it. I've even tried to run this based on the underlying query of "Campaign - Last Contact Status subform" with the same results.
Can someone help me? All I want to do is open the form based on the criteria I specify which is contained in a field in the subform. It doesn't sound that hard but it seems to be getting the better of me!
View 4 Replies
View Related
May 2, 2005
Hi,
I've posted this same question a few months ago, started something else and forgotten about the problem, now I really need to solve this! (I had no luck with the original post - code still not working).
I want to apply a filter to my main form based on criteria showing on a subform. This is the code I've got (applied to the OnOpen event of the main form):
Code:Private Sub Form_Open(Cancel as Integer)Dim stDocName As StringDim stLinkCriteria As String stDocName = "Campaign Form"stLinkCriteria = "[Forms]![Campaign - Last Contact Status subform]![Communication Response]=1" DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormalEnd Sub
The main form is called Campaign Form
The subform is called Campaign - Last Contact Status subform
The field I want to filter on is Communication Response
The criteria I want to filter is : I only want to show records from the Campaign Form (main form) where the status of the Communication Response on the Campaign - Last Contact Status subform (subform) is equal to 1.
I've tried a few variations of the above code but the results are always the same - prompting to enter a paramater value. Now I'm just fishing and hoping I'll get it right.
FYI. the subform only contains one record for each record on the main form. It works a bit like this :
Customer||Date||Communication Response(1=open, 2=closed)
One||20/4||1
Two||21/4||2
Three||21/4||1
One||23/4||2
The corresponding record on the subform for customer One will show the communication response of 2 as this is the most recent date. Therefore you now have the following :
Customer||Communication Response
One||2
Two||2
Three||1
Now I want to only show those records that have a Communication Response of 1. Remember this field appears on a subform. This is the part I am having trouble coding.
I know I've probably just explained the most irrelavent details but if anyone can help me it would be greatly appreciated!!
View 1 Replies
View Related
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
Feb 11, 2014
I am trying to use this code to filter a continuous subform based on two criteria but getting a data type mismatch error.
It works fine if I filter by just one or another. Perhaps it is not possible
Code:
Dim intSpouseEntityID As Integer
intSpouseEntityID = Nz(DLookup("[EntityID]", "qryEntitiesLocations", "[ContactIDNumber] =" & Me.Spouse), 0)
If intSpouseEntityID > 0 And Not IsNull(Me.subformContactsHomeAddress.Form.EntityID) Then
MsgBox ("There are two spouse addresses please delete one and try again")
DoCmd.Save
DoCmd.OpenForm "frmContactAddresses", , , "EntityID=" & Me.txtEntityID Or "EntityID =" & intSpouseEntityID
End If
View 2 Replies
View Related
May 3, 2014
I have a text box ( Supplier_Name) on the main form i would like to filter subform by any part of entered charactor on the feild (suplier_Name)
Code:
Private Sub Find_Click()
If Not IsNull(Supplier_Name) Then
Me.Suppliers_Details.Form.Filter = "[Supplier_Name] = '" & Me.Supplier_Name & "'"
.FilterOn = True
Exit Sub
End If
End Sub
this code is work fine but i have to enter all the characters of long name , but i would like to only insert few chars.
View 2 Replies
View Related
