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


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




:

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
 

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

Top