If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Filtering records on a form using multiple combo boxes
List boxes don't have a Filter property, so you will need to assign their
RowSource. The RowSource is typically a SQL statement. The filter string becomes the WHERE clause that you patch into the middle of the string, after the FROM clause, and before the ORDER BY clause. This kind of thing: Dim strSql As String strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;" Me.List1.RowSource = strSql -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BIGRED56" wrote in message ... Hi ALLEN, I have a question about this coding I have 3 combo boxes and alist which filter another list that displays records If I wanted to write this code How would I do it to filter the record display list box rather then the form... Bigred "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
#12
|
|||
|
|||
Filtering records on a form using multiple combo boxes
Hi allen, this is what i am looking to do...
I am doing a final display page for my database. I t will be used for the purpose of looking up already entered records. What I have is 3 unbounded combo boxes that all independently filter a list box to display certain records.. - these 3 combo boxes have command buttons to activate the filtering I then have 2 text boxes(txtbegin and txtend), which uses its own query to filter the field (Date Located ) in the record list box.. What I would like to do is have the Date box dependent on the other 3 combo boxes. So you can have a date range and another combo filter, filtering the final List display "Allen Browne" wrote: List boxes don't have a Filter property, so you will need to assign their RowSource. The RowSource is typically a SQL statement. The filter string becomes the WHERE clause that you patch into the middle of the string, after the FROM clause, and before the ORDER BY clause. This kind of thing: Dim strSql As String strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;" Me.List1.RowSource = strSql -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BIGRED56" wrote in message ... Hi ALLEN, I have a question about this coding I have 3 combo boxes and alist which filter another list that displays records If I wanted to write this code How would I do it to filter the record display list box rather then the form... Bigred "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
#13
|
|||
|
|||
Filtering records on a form using multiple combo boxes
So, then, you will need to build the Where string from that information,
then build the SQL string with the Where clause in it, and assign the string to the RowSource of the list box. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BIGRED56" wrote in message ... Hi allen, this is what i am looking to do... I am doing a final display page for my database. I t will be used for the purpose of looking up already entered records. What I have is 3 unbounded combo boxes that all independently filter a list box to display certain records.. - these 3 combo boxes have command buttons to activate the filtering I then have 2 text boxes(txtbegin and txtend), which uses its own query to filter the field (Date Located ) in the record list box.. What I would like to do is have the Date box dependent on the other 3 combo boxes. So you can have a date range and another combo filter, filtering the final List display "Allen Browne" wrote: List boxes don't have a Filter property, so you will need to assign their RowSource. The RowSource is typically a SQL statement. The filter string becomes the WHERE clause that you patch into the middle of the string, after the FROM clause, and before the ORDER BY clause. This kind of thing: Dim strSql As String strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;" Me.List1.RowSource = strSql "BIGRED56" wrote in message ... Hi ALLEN, I have a question about this coding I have 3 combo boxes and alist which filter another list that displays records If I wanted to write this code How would I do it to filter the record display list box rather then the form... Bigred "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
#14
|
|||
|
|||
Filtering records on a form using multiple combo boxes
How would I impliment the between function into the sql..
"Allen Browne" wrote: So, then, you will need to build the Where string from that information, then build the SQL string with the Where clause in it, and assign the string to the RowSource of the list box. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BIGRED56" wrote in message ... Hi allen, this is what i am looking to do... I am doing a final display page for my database. I t will be used for the purpose of looking up already entered records. What I have is 3 unbounded combo boxes that all independently filter a list box to display certain records.. - these 3 combo boxes have command buttons to activate the filtering I then have 2 text boxes(txtbegin and txtend), which uses its own query to filter the field (Date Located ) in the record list box.. What I would like to do is have the Date box dependent on the other 3 combo boxes. So you can have a date range and another combo filter, filtering the final List display "Allen Browne" wrote: List boxes don't have a Filter property, so you will need to assign their RowSource. The RowSource is typically a SQL statement. The filter string becomes the WHERE clause that you patch into the middle of the string, after the FROM clause, and before the ORDER BY clause. This kind of thing: Dim strSql As String strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;" Me.List1.RowSource = strSql "BIGRED56" wrote in message ... Hi ALLEN, I have a question about this coding I have 3 combo boxes and alist which filter another list that displays records If I wanted to write this code How would I do it to filter the record display list box rather then the form... Bigred "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
#15
|
|||
|
|||
Filtering records on a form using multiple combo boxes
this is what i have for one of the command buttons
Private Sub Command 73_click() Me.list4.RowSource = "Select.....From....Where table.AreaOfLocation like '*" & ME.Combo58 &"*' between forms!FinalDisplay!txtBegin And forms!FinalDisplay!txtEnd" (FinalDisplay) = page the text boxes are on [happens to be be same page] (txtBegin and txtEnd ) = date text boxes This code does not work but it is a start do you think you can help me fix it.. "Allen Browne" wrote: So, then, you will need to build the Where string from that information, then build the SQL string with the Where clause in it, and assign the string to the RowSource of the list box. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BIGRED56" wrote in message ... Hi allen, this is what i am looking to do... I am doing a final display page for my database. I t will be used for the purpose of looking up already entered records. What I have is 3 unbounded combo boxes that all independently filter a list box to display certain records.. - these 3 combo boxes have command buttons to activate the filtering I then have 2 text boxes(txtbegin and txtend), which uses its own query to filter the field (Date Located ) in the record list box.. What I would like to do is have the Date box dependent on the other 3 combo boxes. So you can have a date range and another combo filter, filtering the final List display "Allen Browne" wrote: List boxes don't have a Filter property, so you will need to assign their RowSource. The RowSource is typically a SQL statement. The filter string becomes the WHERE clause that you patch into the middle of the string, after the FROM clause, and before the ORDER BY clause. This kind of thing: Dim strSql As String strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;" Me.List1.RowSource = strSql "BIGRED56" wrote in message ... Hi ALLEN, I have a question about this coding I have 3 combo boxes and alist which filter another list that displays records If I wanted to write this code How would I do it to filter the record display list box rather then the form... Bigred "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
#16
|
|||
|
|||
Filtering records on a form using multiple combo boxes
I am trying to do the same thing, but am getting no where. Please help!
Fields: Start Date End Date Issue Agent SLR I would like it to filter the report based on at least one to all of the criteria. Also, how do I set up the query to filter. I've tried the Like options, but it only filters one criteria and not all. "Allen Browne" wrote: The example below shows how to build up a filter string from the non-blank boxes. You can then use the same filter string to output a report of the same records. The example assumes the CompanyID is a Number type field. If it is a Text type field, you need extra quotes: strWhere = strWhere & "([CompanyID] = """ & _ Me.cboFindCompanyID & """) AND " With the date range, if you supply you get: Only a start date all records from that date on Only an end date all records up to that date Both all records between the dates Neither all records. The example is crafted so that it is dead easy to add more search boxes if needed. Each one tacks an " AND " on the end ready for the next one, and the trailing " AND " is chopped off at the end. If no criteria were found, the code returns all records. Here is the code to filter the form: --------------filter code starts------------------ Dim strWhere As String Dim lngLen As Long Const conDateFormat = "\#mm\/dd\/yyyy\#" If Me.Dirty Then Me.Dirty = False 'Save first. If Not IsNull(Me.cboFindCompanyID) Then strWhere = strWhere & "([CompanyID] = " & _ Me.cboFindCompanyID & ") AND " End If If Not IsNull(Me.cboFindAgentID) Then strWhere = strWhere & "([AgentID] = " & _ Me.cboFindAgentID & ") AND " End If If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = "([LeadDate] " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = "([LeadDate] " & _ Format(Me.txtStartDate, conDateFormat) & ") AND " Else 'Both start and end dates. strWhere = "([LeadDate] Between " & _ Format(Me.txtStartDate, conDateFormat) & " And " & _ Format(Me.txtEndDate, conDateFormat) & ") AND " End If End If 'Chop off the trailing " AND ". lngLen = Len(strWhere) - 5 If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False Endif --------------filter code ends------------------ Now, if you wanted to print the same records, your command button's Click event procedure would be: Dim strWhere As String If Me.Dirty Then Me.Dirty = False If Me.FilterOn Then strWhere = Me.Filter DoCmd.OpenReport "Report1", acViewPreview, , strWhere -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kevin Kraemer" wrote in message ... Welcome Weekend Warriors! I am pretty good with Access, not so such with VB code. I have a Marketing / Sales Lead table which includes fields like LeadDate (Date the lead comes in) MarketingCampaignID (foreign key from the Marketing Campaign table) CompanyID (foreign key from the Company table) AgentID (foreign key from the Agent table) Finally a PRINT checkbox. I have a form which brings in all records and fields from the Sales Lead table. From there I want to be able to filter the records on the form based on a date range as well as selections in the combo boxes (all located in the header) of a continuous form. The date fields are text boxes (StartDate and EndDate) - records whose LeadDate is between those 2 date text boxes I would like to filter and show only those filtered records on the form. Then I have 3 unbound combo boxes with the row source being the main table of the combo box. For the Agent Combo Box (AgentCB), the row source is the Agent table, with the bound field being 1 (which is the primary key called AgentID). Once an agent is selected (let's say Agent 1), I would like to filter the current records in the form based on the agent ID (while continuing to filter on the date text boxes). The other 3 combo boxes - one for the company and one for the marketing campaigns are set up the same way. After a person selects an agent, they may also want to select a marketing campaign (let's say Magazine Ad) - I would like the form to display records for the date range for Agent 1 for the Magazine ad. Hopefully that is descriptive enough to understand. Finally I have them click on a PRINT checkbox, since they may only want to include some of the records. What I also have is a Print All button so all the check boxes get checked. I had run an update query to do this in the past, but I don't know how to do that with the multiple filters. Thanks ahead of time for your help |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filtering records using combo boxes on a form | 576422 | General Discussion | 1 | December 1st, 2005 06:06 AM |
FOrm and COmbo Box... | NWO | Using Forms | 17 | November 20th, 2005 05:47 AM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Filtering records using multiple combo boxes | Mark Senibaldi | Using Forms | 0 | June 17th, 2004 03:55 PM |
Filtering Records using multiple combo boxes | Mark Senibaldi | Using Forms | 0 | June 17th, 2004 03:51 PM |