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
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.
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?
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.
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?
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??
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".
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.
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?
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];
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!
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!!
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.
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?
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
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.
DCount("*","[tblLeaveRequests]","Status='Approved' And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")
The above code counts all leave with the status of approved on the table [tblLeaveRequests]. Great, but i did forget a critera.
Of the leave it is counting, I want it to only count if the employee the leave is for is in a particular location. which exists on the related table, and not the one dcount is calling.
Is this possible? or will i have to resort to replicated data in [tblLeaveRequests]
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
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]).
I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.
I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".
Private Sub Run_Report_Click() Dim StrWhere As String Dim varItem As Variant
If Me.Market.ItemsSelected.Count > 0 Then
[Code] .....
Questions: 1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box. 2. What is the syntax to add in the other combo box selections?
I have a Master form which includes tabs within the tab I have subforms. I am looking for a way to filter my master form based on data found within my sub form.
I have a check box within one of the sub-forms called "softwareInstall" if yes the box would be checked. I want to filter all customers within my main form with a check box in my main form to turn on and show all customer who has this box check within the subform SoftwareInstall=True.
Private Sub btncard_Click() Dim stLinkCriteria As String DoCmd.RunCommand acCmdSaveRecord stLinkCriteria = "[jobref]=" & "'" & Me![jobref] & "'" DoCmd.Close DoCmd.OpenForm "k_job_card", , , stLinkCriteria End Sub
this button code opens the k_job_card form.
the form opens and says 'Record 1 of 1, (Filtered)
i have a control on the form that opens.. and i am simply trying to assign a default value to that control..
it is a textbox.. for my jobtype field (text datatype)..
is there any particular reason why i cannot assign a default value to a control if it has been placed on a form that has been opened using a form filter?
this is how the Form Data tab is set at the moment..
