Report errors from a form to select names and dates with...

[Arnold]

Greetings Gurus,

In a report showing the names of students and their progress, I am
getting an error in the name field (Name: #Error). The report gets its
data from an unbound form containing two unbound textboxes,
"txtStartDate" and "txtEndDate," and a multi-select listbox,
"listName." I think the error occurs when there is no correct
combination of data--for instance, no records for John Doe entered
during this past week.

This is my 1st attempt at making a form allowing a user to set multiple
criteria. I've tried to adapt code from Allen Browne's site and other
posts, but don't really know what I'm doing. I'd like for users to
select mutliple students (or ALL students) from the listbox and specify
a date range (which isn't required).

The report's name is rptProgressReports.
The form's name is frmSelectReport.
The listbox's name is listname.
The listbox's row source is
SELECT Students.StudentID, Students.LastName, Students.FirstName FROM
Students ORDER BY Students.LastName;

The command button's on click event is:

On Error GoTo Err_cmdOK_Click

Dim i As Integer
Dim stDocName As String
Dim stWhere As String
Dim sTest As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

stDocName = "rptProgressReports"

For i = 0 To listName.ItemsSelected.Count - 1
If stWhere <> "" Then stWhere = stWhere & ", "
stWhere = stWhere & listName.Column(0, listName.ItemsSelected(i))
Next

strDate = "Date" 'Date is the name of field in the detail section
of the report.

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

If stWhere <> "" Then
If sTest <> "" Then sTest = sTest & " AND "
sTest = sTest & "StudentID IN (" & stWhere & ")"
End If

DoCmd.OpenReport stDocName, acViewPreview, , sTest

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_cmdOK_Click
End Sub

An example of the filter that was input into the Report's filter is:
(Date Between #01/23/2006# And #01/29/2006# AND StudentID IN (7))
The record source of the report is a query containing StudentID,
LastName, FirstName, ProgressID, Progress, Date, ClassID, and Class.

In terms of selecting ALL students from the listbox, is it possible to
do this with a union query involving multiple columns in the listbox?

Thanks,

Arnold

[Bob Quintal]

Your first problem is that DATE is a reserved word in MSAccess.
(Date Between #01/23/2006# And #01/29/2006#
AND StudentID IN (7))

you also say 'Date is the name of field in the detail

section of the report'
It's the name of the field in the query underlying the report
that you need to use. It's not clear that they are the same, if
not, adjust accordingly.
The answer to the question about adding "< ALL Sudents > to a
listbox is YES.

Q

"Arnold" <ee*******@kc.rr.com> wrote in
news:11*********************@g43g2000cwa.googlegro ups.com:
Greetings Gurus,

In a report showing the names of students and their progress,
I am getting an error in the name field (Name: #Error). The
report gets its data from an unbound form containing two
unbound textboxes, "txtStartDate" and "txtEndDate," and a
multi-select listbox, "listName." I think the error occurs
when there is no correct combination of data--for instance, no
records for John Doe entered during this past week.

This is my 1st attempt at making a form allowing a user to set
multiple criteria. I've tried to adapt code from Allen
Browne's site and other posts, but don't really know what I'm
doing. I'd like for users to select mutliple students (or ALL
students) from the listbox and specify a date range (which
isn't required).

The report's name is rptProgressReports.
The form's name is frmSelectReport.
The listbox's name is listname.
The listbox's row source is
SELECT Students.StudentID, Students.LastName,
Students.FirstName FROM Students ORDER BY Students.LastName;

The command button's on click event is:

On Error GoTo Err_cmdOK_Click

Dim i As Integer
Dim stDocName As String
Dim stWhere As String
Dim sTest As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

stDocName = "rptProgressReports"

For i = 0 To listName.ItemsSelected.Count - 1
If stWhere <> "" Then stWhere = stWhere & ", "
stWhere = stWhere & listName.Column(0,
listName.ItemsSelected(i)) Next

strDate = "Date" 'Date is the name of field in the detail
section of the report.

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

If stWhere <> "" Then
If sTest <> "" Then sTest = sTest & " AND "
sTest = sTest & "StudentID IN (" & stWhere & ")"
End If

DoCmd.OpenReport stDocName, acViewPreview, , sTest

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_cmdOK_Click
End Sub

An example of the filter that was input into the Report's
filter is: (Date Between #01/23/2006# And #01/29/2006# AND
StudentID IN (7)) The record source of the report is a query
containing StudentID, LastName, FirstName, ProgressID,
Progress, Date, ClassID, and Class.

In terms of selecting ALL students from the listbox, is it
possible to do this with a union query involving multiple
columns in the listbox?

Thanks,

Arnold

--
Bob Quintal

PA is y I've altered my email address.

[Arnold]

Thanks for the tip. I renamed the controls that were simply "date."

However, this didn't fix the problem where if a student is selected
from the listbox on the form, but no progress records were previously
entered for that student, the report generates but with an error for
the name's field.

Any other ideas?--all are greatly appreciated. Thanks.