Filtering records on a form using multiple combo boxes

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

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

Allen - Thank you - the code worked ... I had to move the date code to the top.
I have 3 more issues: (maybe be a little lengthy)

Background: I put a button on my form "filter records" with the following code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the filtered
results. People will be using MS-Word to use the results from these filtered
records. I figured if I could perform a make table query, and then a query
based on that table for MS-Word to use, that should do it.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox (print)
with the filtered records. The functionality today on the form has a "print"
checkbox. This way they are able to select only some of the records, but I
also want to include a button for "Select All To Print" - The report then
looks to see if the PRINT checkbox is set to yes.

3). I tried to include th functionality to "reset the filters" incase
someone wants to change something, or start fresh. So I put a put a button
on the form to "reset filters" with the following code (it includes setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

(end code)

This seems to work, I get all the records to display again, and all the date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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

Responses in-line.

--
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
...
Allen - Thank you - the code worked ... I had to move the date code to the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can set up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox (print)
with the filtered records. The functionality today on the form has a
"print"
checkbox. This way they are able to select only some of the records, but
I
also want to include a button for "Select All To Print" - The report then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked), you can
check/uncheck individual records for the report. You can set all filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked = True;"

3). I tried to include th functionality to "reset the filters" incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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

Allen - Thanks again for your quick responses ... I got 2 of the 3 solutions
too work - but I get an error on the updating the filtered records

The yes/no box is titled Print
The table I am updating is called SalesLeads

this is the code: (I added the DIM statement, wasn't sure if I needed to -
but it gives me the same error regardless).

CODE: (I have a button to run this - onclick)

Dim strSQL As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSQL = "UPDATE SalesLeads SET Print = True"
If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
End If
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError
Me.Requery

I get this error:
Too few parameters. Expected 1

FYI - the code works if I don't apply any of the filters.

Thanks in advance ...
Kevin




"Allen Browne" wrote:

Responses in-line.

--
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
...
Allen - Thank you - the code worked ... I had to move the date code to the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can set up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox (print)
with the filtered records. The functionality today on the form has a
"print"
checkbox. This way they are able to select only some of the records, but
I
also want to include a button for "Select All To Print" - The report then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked), you can
check/uncheck individual records for the report. You can set all filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked = True;"

3). I tried to include th functionality to "reset the filters" incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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.

Okay, so Access is unable to execute the string with the Filter in place.

To track down the cause, we need to know what is in the form's Filter.
Immediately above the Execute line, add the line:
Debug.Print strSQL
When it fails, press Ctrl+G to open the immediate window, and see what's
there.

If this is Access 2002 or 2003, you might find that the filter includes a
reference to a lookup table. If so, you will need to craft the SQL string so
that it includes the lookup table as well, or change the filter so it does
not include the lookup table.

If the filter contains a reference such as:
Forms![Form2][Text5]
you could try:
DoCmd.RunSQL strSQL
in place of
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError

--
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
...
Allen - Thanks again for your quick responses ... I got 2 of the 3
solutions
too work - but I get an error on the updating the filtered records

The yes/no box is titled Print
The table I am updating is called SalesLeads

this is the code: (I added the DIM statement, wasn't sure if I needed to -
but it gives me the same error regardless).

CODE: (I have a button to run this - onclick)

Dim strSQL As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSQL = "UPDATE SalesLeads SET Print = True"
If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
End If
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError
Me.Requery

I get this error:
Too few parameters. Expected 1

FYI - the code works if I don't apply any of the filters.

Thanks in advance ...
Kevin

"Allen Browne" wrote:

Responses in-line.

"Kevin Kraemer" wrote in message
...
Allen - Thank you - the code worked ... I had to move the date code to
the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of
concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the
following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the
filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can set
up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox
(print)
with the filtered records. The functionality today on the form has a
"print"
checkbox. This way they are able to select only some of the records,
but
I
also want to include a button for "Select All To Print" - The report
then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked), you
can
check/uncheck individual records for the report. You can set all filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked = True;"

3). I tried to include th functionality to "reset the filters" incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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.

Allen

Thanks ... had to work on another project for a few days ...

I did what you suggested - the immediate window shows

UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2)
This is correct based on what I selected in the combo box

I did try this ... I selected values in 2 combo boxes. Now the error reads
Too Few Parameters. Expected 2 (used to 1)
The immediate window then reads:
UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2) AND
([MarketingCampaignAutoID] = 2)

I tried the other code:
DoCmd.RunSQL strSQL
This runs without any errors, but updates ALL the records in the table not
just the filtered ones.

Thanks again

Kevin

"Allen Browne" wrote:

Okay, so Access is unable to execute the string with the Filter in place.

To track down the cause, we need to know what is in the form's Filter.
Immediately above the Execute line, add the line:
Debug.Print strSQL
When it fails, press Ctrl+G to open the immediate window, and see what's
there.

If this is Access 2002 or 2003, you might find that the filter includes a
reference to a lookup table. If so, you will need to craft the SQL string so
that it includes the lookup table as well, or change the filter so it does
not include the lookup table.

If the filter contains a reference such as:
Forms![Form2][Text5]
you could try:
DoCmd.RunSQL strSQL
in place of
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError

--
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
...
Allen - Thanks again for your quick responses ... I got 2 of the 3
solutions
too work - but I get an error on the updating the filtered records

The yes/no box is titled Print
The table I am updating is called SalesLeads

this is the code: (I added the DIM statement, wasn't sure if I needed to -
but it gives me the same error regardless).

CODE: (I have a button to run this - onclick)

Dim strSQL As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSQL = "UPDATE SalesLeads SET Print = True"
If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
End If
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError
Me.Requery

I get this error:
Too few parameters. Expected 1

FYI - the code works if I don't apply any of the filters.

Thanks in advance ...
Kevin

"Allen Browne" wrote:

Responses in-line.

"Kevin Kraemer" wrote in message
...
Allen - Thank you - the code worked ... I had to move the date code to
the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of
concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the
following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the
filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can set
up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox
(print)
with the filtered records. The functionality today on the form has a
"print"
checkbox. This way they are able to select only some of the records,
but
I
also want to include a button for "Select All To Print" - The report
then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked), you
can
check/uncheck individual records for the report. You can set all filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked = True;"

3). I tried to include th functionality to "reset the filters" incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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 "

Copy the SQL statement from the immediate window.
Create a new query.
Switch the query to SQL View (View menu.)
Paste the SQL string in.
Switch the query to Design view.

Can you see what is wrong?
For example, is there a field named "CompanyAutoID" in the SalesLeads table?
Or is this field name spelled differently? Does Access add quotes around the
"2" in the criteria of the query? What happens if you try to run the query
from the query window (Exclamation icon on toolbar)?

--
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
...
Allen

Thanks ... had to work on another project for a few days ...

I did what you suggested - the immediate window shows

UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2)
This is correct based on what I selected in the combo box

I did try this ... I selected values in 2 combo boxes. Now the error
reads
Too Few Parameters. Expected 2 (used to 1)
The immediate window then reads:
UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2) AND
([MarketingCampaignAutoID] = 2)

I tried the other code:
DoCmd.RunSQL strSQL
This runs without any errors, but updates ALL the records in the table not
just the filtered ones.

Thanks again

Kevin

"Allen Browne" wrote:

Okay, so Access is unable to execute the string with the Filter in place.

To track down the cause, we need to know what is in the form's Filter.
Immediately above the Execute line, add the line:
Debug.Print strSQL
When it fails, press Ctrl+G to open the immediate window, and see what's
there.

If this is Access 2002 or 2003, you might find that the filter includes a
reference to a lookup table. If so, you will need to craft the SQL string
so
that it includes the lookup table as well, or change the filter so it
does
not include the lookup table.

If the filter contains a reference such as:
Forms![Form2][Text5]
you could try:
DoCmd.RunSQL strSQL
in place of
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError

--
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
...
Allen - Thanks again for your quick responses ... I got 2 of the 3
solutions
too work - but I get an error on the updating the filtered records

The yes/no box is titled Print
The table I am updating is called SalesLeads

this is the code: (I added the DIM statement, wasn't sure if I needed
to -
but it gives me the same error regardless).

CODE: (I have a button to run this - onclick)

Dim strSQL As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSQL = "UPDATE SalesLeads SET Print = True"
If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
End If
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError
Me.Requery

I get this error:
Too few parameters. Expected 1

FYI - the code works if I don't apply any of the filters.

Thanks in advance ...
Kevin

"Allen Browne" wrote:

Responses in-line.

"Kevin Kraemer" wrote in
message
...
Allen - Thank you - the code worked ... I had to move the date code
to
the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of
concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the
following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the
filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then
a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can
set
up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need
to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox
(print)
with the filtered records. The functionality today on the form has
a
"print"
checkbox. This way they are able to select only some of the
records,
but
I
also want to include a button for "Select All To Print" - The
report
then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked),
you
can
check/uncheck individual records for the report. You can set all
filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked =
True;"

3). I tried to include th functionality to "reset the filters"
incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all
the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin


"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 "

Allen

Just wanted to thank you for all your help ... Based on what you said, I
looked at the field names in the SalesLeads table, and noticed a difference
in field names - i.e. - the unbound combo box (for the filter) has
CompanyAutoID, but the foreign key in the SalesLeads table was CompanyID --
once I changed the field name in SalesLead table to CompanyAUTOid, the code
worked fine ... thanks again

"Allen Browne" wrote:

Copy the SQL statement from the immediate window.
Create a new query.
Switch the query to SQL View (View menu.)
Paste the SQL string in.
Switch the query to Design view.

Can you see what is wrong?
For example, is there a field named "CompanyAutoID" in the SalesLeads table?
Or is this field name spelled differently? Does Access add quotes around the
"2" in the criteria of the query? What happens if you try to run the query
from the query window (Exclamation icon on toolbar)?

--
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
...
Allen

Thanks ... had to work on another project for a few days ...

I did what you suggested - the immediate window shows

UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2)
This is correct based on what I selected in the combo box

I did try this ... I selected values in 2 combo boxes. Now the error
reads
Too Few Parameters. Expected 2 (used to 1)
The immediate window then reads:
UPDATE SalesLeads SET Print = True WHERE ([CompanyAutoID] = 2) AND
([MarketingCampaignAutoID] = 2)

I tried the other code:
DoCmd.RunSQL strSQL
This runs without any errors, but updates ALL the records in the table not
just the filtered ones.

Thanks again

Kevin

"Allen Browne" wrote:

Okay, so Access is unable to execute the string with the Filter in place.

To track down the cause, we need to know what is in the form's Filter.
Immediately above the Execute line, add the line:
Debug.Print strSQL
When it fails, press Ctrl+G to open the immediate window, and see what's
there.

If this is Access 2002 or 2003, you might find that the filter includes a
reference to a lookup table. If so, you will need to craft the SQL string
so
that it includes the lookup table as well, or change the filter so it
does
not include the lookup table.

If the filter contains a reference such as:
Forms![Form2][Text5]
you could try:
DoCmd.RunSQL strSQL
in place of
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError

--
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
...
Allen - Thanks again for your quick responses ... I got 2 of the 3
solutions
too work - but I get an error on the updating the filtered records

The yes/no box is titled Print
The table I am updating is called SalesLeads

this is the code: (I added the DIM statement, wasn't sure if I needed
to -
but it gives me the same error regardless).

CODE: (I have a button to run this - onclick)

Dim strSQL As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSQL = "UPDATE SalesLeads SET Print = True"
If Me.FilterOn Then
strSQL = strSQL & " WHERE " & Me.Filter
End If
DBEngine(0)(0).Execute strSQL & ";", dbFailOnError
Me.Requery

I get this error:
Too few parameters. Expected 1

FYI - the code works if I don't apply any of the filters.

Thanks in advance ...
Kevin

"Allen Browne" wrote:

Responses in-line.

"Kevin Kraemer" wrote in
message
...
Allen - Thank you - the code worked ... I had to move the date code
to
the
top.
I have 3 more issues: (maybe be a little lengthy)

If you wanted the date code lower down, it's just a matter of
concatenating
the string to whatever is already in it in each of the 3 cases, e.g.:
strWhere = strWhere & "([Date of Lead] ...

Background: I put a button on my form "filter records" with the
following
code

Private Sub Filter_Records_Click()
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = "([Date of Lead] " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = "([Date of Lead] " & _
Format(Me.StartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([Date of Lead] Between " & _
Format(Me.StartDate, conDateFormat) & " And " & _
Format(Me.EndDate, conDateFormat) & ") AND "
End If
End If

If Not IsNull(Me.CompanyCB) Then
strWhere = strWhere & "([CompanyAutoID] = " & _
Me.CompanyCB & ") AND "
End If

If Not IsNull(Me.UserCB) Then
strWhere = strWhere & "([UserAutoID] = " & _
Me.UserCB & ") AND "
End If

If Not IsNull(Me.MarketingCampaignCB) Then
strWhere = strWhere & "([MarketingCampaignAutoID] = " & _
Me.MarketingCampaignCB & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

THIS CODE WORKS FINE.

1). I would like code to perform a Make Table Query based on the
filtered
results. People will be using MS-Word to use the results from these
filtered
records. I figured if I could perform a make table query, and then
a
query
based on that table for MS-Word to use, that should do it.

How about an Append query instead of a Make Table? That way you can
set
up
the table with the fields you want, delete any existing records, and
populate it based on the Filter of the form.

This example assumes the temp table is called Table1:
Dim db As DAO.Database
Dim strSql as String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1;"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO Table1 (...
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter & ";"
End If
db.Execute strSql, dbFailOnError
Set db = Nothing

To get the SQL statement, you could:
1. Mock up a query to select the fields from the source table (not
wildcard).
2. Change it to an Append query (Append on query menu).
3. Map the fields correctly.
4. Switch to SQL View. There's an example of the SQL string you need
to
create.

2). Thank you for including the code to print a report based on the
filtering. If possible I would like the code to update a checkbox
(print)
with the filtered records. The functionality today on the form has
a
"print"
checkbox. This way they are able to select only some of the
records,
but
I
also want to include a button for "Select All To Print" - The
report
then
looks to see if the PRINT checkbox is set to yes.

Yes. If you add a yes/no field to your table (named, say, IsPicked),
you
can
check/uncheck individual records for the report. You can set all
filtered
records to True like this:
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
strSql = "UPDATE Table2 SET IsPicked = True"
If Me.FilterOn Then
strSql = strSql & " WHERE " & Me.Filter
End If
dbEngine(0)(0).Execute strSql & ";", dbFailOnError
Me.Requery

To set *all* records to false again, execute this Update query string:
strSql = ""UPDATE Table2 SET IsPicked = False WHERE IsPicked =
True;"

3). I tried to include th functionality to "reset the filters"
incase
someone wants to change something, or start fresh. So I put a put a
button
on the form to "reset filters" with the following code (it includes
setting
all the boxes to null).

The Code is:
Me.FilterOn = True
Me.StartDate = ""
Me.EndDate = ""
Me.CompanyCB = ""
Me.MarketingCampaignCB = ""
Me.UserCB = ""
DoCmd.ShowAllRecords
Requery

Set them to Null, not a zero-length string, e.g.:
Me.StartDate = Null
Me.EndDate = Null

(end code)

This seems to work, I get all the records to display again, and all
the
date
boxes and the Combo boxes have nothing in them.

Next I select a value in one of the combo boxes, and hit the "filter
Records" button, which runs the code from above --

I get an error
Run Time Error 2448
You cannot assign a value to this object

When I Debug, it shows the line of code ...

Me.Filter = Left(strWhere, lngLen)

please advise ...

Thanks again for all of your help

Kevin

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