Allen Browne / Filter your report by more criteria

D

dk

Allen Browne provides the sample code below to allow filtering a report based on dates. If, IN ADDITION TO filtering a report between two dates, one wanted to filter a form based on another field (in my case, Author), what elements would you add to the code below? I keep trying but it filters correctly by date and not by my author field.

thanks

The code he provides is:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
M

Michael Keating

Hi,

I think your problem is that the where condition created here is meant as a
standalone in as much as it has no parentheses around it, so you'll run up
against logic problems if you just add another conditional value to the end.

You should be able to get around this by surrounding the original where with
parentheses, then "anding" the new conditional, so the new code would be
something like:


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strWhere = strWhere & ")"
'now add the new conditional

strWhere = strWhere & " AND ([AuthorField] = " & Me.txtAuthor.Text & ")"

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

HTH


MFK.


dk said:
Allen Browne provides the sample code below to allow filtering a report
Click to expand...
based on dates. If, IN ADDITION TO filtering a report between two dates,
one wanted to filter a form based on another field (in my case, Author),
what elements would you add to the code below? I keep trying but it filters
correctly by date and not by my author field.
 
D

dk

Actually sorry, my code didn't exactly follow your suggestions and I made a few corrections but I get the error:
"You can reference a property or method for a control that doesn't have focus"
if I try to enter a SetFocus argument for cboAuthor (which I'm guessing it refers to), I get the syntax error mentioned earlier.

Here is what my code looks like right now:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & "AND ([Author]=" & Me.cboAuthor.Text & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere




Michael Keating said:
Hi,

I think your problem is that the where condition created here is meant as a
standalone in as much as it has no parentheses around it, so you'll run up
against logic problems if you just add another conditional value to the end.

You should be able to get around this by surrounding the original where with
parentheses, then "anding" the new conditional, so the new code would be
something like:


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strWhere = strWhere & ")"
'now add the new conditional

strWhere = strWhere & " AND ([AuthorField] = " & Me.txtAuthor.Text & ")"

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

HTH


MFK.


dk said:
Allen Browne provides the sample code below to allow filtering a report
Click to expand...
based on dates. If, IN ADDITION TO filtering a report between two dates,
one wanted to filter a form based on another field (in my case, Author),
what elements would you add to the code below? I keep trying but it filters
correctly by date and not by my author field.
thanks

The code he provides is:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Click to expand...
Click to expand...
 
A

Allen Browne

That error is caused by referring to the Text property. While that is the
typical approach in pure VB, in Access you need the Value property instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
dk said:
Actually sorry, my code didn't exactly follow your suggestions and I made
Click to expand...
a few corrections but I get the error:
"You can reference a property or method for a control that doesn't have focus"
if I try to enter a SetFocus argument for cboAuthor (which I'm guessing it
Click to expand...
refers to), I get the syntax error mentioned earlier.
Here is what my code looks like right now:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " &
Click to expand...
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " &
Click to expand...
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Click to expand...
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & "AND ([Author]=" & Me.cboAuthor.Text & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere




Michael Keating said:
Hi,

I think your problem is that the where condition created here is meant as a
standalone in as much as it has no parentheses around it, so you'll run up
against logic problems if you just add another conditional value to the end.

You should be able to get around this by surrounding the original where with
parentheses, then "anding" the new conditional, so the new code would be
something like:


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strWhere = strWhere & ")"
'now add the new conditional

strWhere = strWhere & " AND ([AuthorField] = " & Me.txtAuthor.Text & ")"

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

HTH


MFK.


dk said:
Allen Browne provides the sample code below to allow filtering a
Click to expand...
report
based on dates. If, IN ADDITION TO filtering a report between two dates,
one wanted to filter a form based on another field (in my case, Author),
what elements would you add to the code below? I keep trying but it filters
correctly by date and not by my author field.
thanks

The code he provides is:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " &
Click to expand...
Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Click to expand...
Click to expand...
Click to expand...
 
D

dk

I keep getting a syntax error, but it's so tiring that my client might just have to do without it. Thanks for all the help. My code looks as below so far. If anyone isn't too exhausted to see if they can spot the problem, I'd be extremely thankful.

Private Sub Command6_Click()


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & " AND ([Author]= " & Me.cboAuthor.Value & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere



Allen Browne said:
That error is caused by referring to the Text property. While that is the
typical approach in pure VB, in Access you need the Value property instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
dk said:
Actually sorry, my code didn't exactly follow your suggestions and I made
Click to expand...
a few corrections but I get the error:
"You can reference a property or method for a control that doesn't have focus"
if I try to enter a SetFocus argument for cboAuthor (which I'm guessing it
Click to expand...
refers to), I get the syntax error mentioned earlier.
Here is what my code looks like right now:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " &
Click to expand...
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " &
Click to expand...
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Click to expand...
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & "AND ([Author]=" & Me.cboAuthor.Text & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere




Michael Keating said:
Hi,

I think your problem is that the where condition created here is meant as a
standalone in as much as it has no parentheses around it, so you'll run up
against logic problems if you just add another conditional value to the end.

You should be able to get around this by surrounding the original where with
parentheses, then "anding" the new conditional, so the new code would be
something like:


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strWhere = strWhere & ")"
'now add the new conditional

strWhere = strWhere & " AND ([AuthorField] = " & Me.txtAuthor.Text & ")"

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

HTH


MFK.


Allen Browne provides the sample code below to allow filtering a report
based on dates. If, IN ADDITION TO filtering a report between two dates,
one wanted to filter a form based on another field (in my case, Author),
what elements would you add to the code below? I keep trying but it filters
correctly by date and not by my author field.

thanks

The code he provides is:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell

Assuming that Author is a text-formatted field, change this line
strWhere = strWhere & " AND ([Author]= " & Me.cboAuthor.Value & ")"

to this line:
strWhere = strWhere & " AND ([Author]= '" & Me.cboAuthor.Value &
"')"

Note the delimiting of the value from the combo box by ' characters. SQL
expects the value for a text-formatted field to be a string, so it must be
delimited by ' or " characters.

--

Ken Snell
<MS ACCESS MVP>

dk said:
I keep getting a syntax error, but it's so tiring that my client might
Click to expand...
just have to do without it. Thanks for all the help. My code looks as below
so far. If anyone isn't too exhausted to see if they can spot the problem,
I'd be extremely thankful.
Private Sub Command6_Click()


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " &
Click to expand...
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " &
Click to expand...
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Click to expand...
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & " AND ([Author]= " & Me.cboAuthor.Value & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere



Allen Browne said:
That error is caused by referring to the Text property. While that is the
typical approach in pure VB, in Access you need the Value property instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
dk said:
Actually sorry, my code didn't exactly follow your suggestions and I
Click to expand...
made
a few corrections but I get the error:
"You can reference a property or method for a control that doesn't
Click to expand...
have
focus"
if I try to enter a SetFocus argument for cboAuthor (which I'm
Click to expand...
guessing it
refers to), I get the syntax error mentioned earlier.
Here is what my code looks like right now:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "JournalReport"
strField = "Date"
strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " &
Click to expand...
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " &
Click to expand...
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Click to expand...
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & ")"
strWhere = strWhere & "AND ([Author]=" & Me.cboAuthor.Text & ")"

DoCmd.OpenReport strReport, acViewPreview, , strWhere




:

Hi,

I think your problem is that the where condition created here is
Click to expand...
meant
as a
standalone in as much as it has no parentheses around it, so you'll
Click to expand...
run
up
against logic problems if you just add another conditional value to
Click to expand...
the
end.
You should be able to get around this by surrounding the original
Click to expand...
where
with
parentheses, then "anding" the new conditional, so the new code would be
something like:


Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

strWhere = "("

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strWhere & strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strWhere & strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

strWhere = strWhere & ")"
'now add the new conditional

strWhere = strWhere & " AND ([AuthorField] = " &
Click to expand...
Me.txtAuthor.Text &
")"
' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere

HTH


MFK.


Allen Browne provides the sample code below to allow filtering a report
based on dates. If, IN ADDITION TO filtering a report between two dates,
one wanted to filter a form based on another field (in my case, Author),
what elements would you add to the code below? I keep trying but it filters
correctly by date and not by my author field.

thanks

The code he provides is:

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strField = "SaleDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging
Click to expand...
purposes
only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error 2
Set date range and filter results via form/menu for Report Preview 12
Trouble with Code for date filter on report... 10
Multiple Report Filter using form 1
Have A Form Open a Query 2
Please help w/form collecting parameters for Report 1
type mismatch error 13 1
Filter Report by date from Form 2

Top