Need Help Adding a 2nd Where Condition

[Guest]

Hello:

I have a little script on a command button that prints the Class Attendance
sheet for the current class from a One to Many form that works great.
However, I want to add another condition to the report’s strWhere condition.
That condition is based upon a child table field: IsNull([Attend_Status])

Here’s the current script that works perfectly.


Private Sub cmdClassAttendance_Click()

On Error GoTo Err_cmdClassAttendance_Click

Dim strDocName As String

Dim strWhere As String

strDocName = "rptClassAttendance"


strWhere = "[ClassNo]= '" & Me.ClassNo & "'"

DoCmd.OpenReport strDocName, acViewPreview, , strWhere


Exit_cmdClassAttendance_Click:

Exit Sub


Err_cmdClassAttendance_Click:

MsgBox Err.Description

Resume Exit_cmdClassAttendance_Click


End Sub

-----------------------------------------------
Here’s the where condition I want to add.
-----------------------------------------------
IsNull([Attend_Status])

Additional Info:

Form Name: frmClasses

SubForm Name: sfrmClassesqrytblLink

Child Table field is [Attend_Status]

Note: Both the parent form and the subform are based upon queries. The
parent form queries tblClasses while the child form queries tblLink.

Thanks,
Robert

 

[Guest]

Oooooooooops....... forgot to mention that both ClassNo and the child field,
Attend_Status, are both TEXT fields.

 

[Guest]

Ooooopsss, forgot to mention that both ClassNo and the child table field,
Attend_Status, are TEXT fields.

 

[Gina Whipp]

How about.... (watch out for line wrap)

Private Sub cmdClassAttendance_Click()
On Error GoTo Err_cmdClassAttendance_Click

If
IsNull(IsNull(Forms![frmClasses]![sfrmClassesqrytblLink]![Attend_Status])
Then
'Do Some Action like a message box so users know what just happened
MsgBox "Nothing to do!", vbInformation, "Class Attendance"
Else
DoCmd.OpenReport "rptClassAttendance", acViewPreview, , "[ClassNo]=
'" & Me.ClassNo & "'"
End if

Exit_cmdClassAttendance_Click:
Exit Sub

HTH,
Gina Whipp

 

[Guest]

Hi Gina:

That was a very cool suggestion, however, it doesn't solve our problem. It
makes me realize that I didn't explain the situation in enough detail.

We have two reports, a Class Attendance sheet that we print out with
everyone's name who is registered for the class. We pass that around during
the class so that everyone who attends can sign the attendance sheet.
However, if a student registers for the class and then cancels her/her
registration, we put the words "Student Canceled" in the Attend_Status field
because we want to keep a record indicating the student registered and
subseqently canceled.

So, when printing the Class Attendance sheet, we don't want to include
anyone who's already canceled. However, when printing the Class Report, we
want to include everyone in the report because we want to retain that
information. Did I explain this clearly?

Anyway, thanks to you, I was able to get the expression working, however, it
still printed the names of students who had the words, "Student Canceled" in
the Attend_Status field. That field was NOT Null so they should have been
excluded from the Class Attendance report. In the end, I got the report to
print but it's not solving our problem.

Here's the expression I used.

strWhere = "[ClassNo]= '" & Me.ClassNo & "' And
IsNull(Forms![frmClasses]![sfrmClassesqrytblLink]![Attend_Status])"

 

[Guest]

Hi Gina:

I thought of another hopefully clearer and easier way to explain what we want.

We only want to print the names of students where

Attend_Status = "Registered"

I think that is a little clearer. We don't want anyone on the class
attendance sheet who canceled their registration.

Robert

 

[Gina Whipp]

Okay, I think I got it now...

Private Sub cmdClassAttendance_Click()
On Error GoTo Err_cmdClassAttendance_Click

If Forms![frmClasses]![sfrmClassesqrytblLink]![Attend_Status] =
"Registered" Then
DoCmd.OpenReport "rptClassAttendance", acViewPreview, , "[ClassNo]=
'" & Me.ClassNo & "'"
Else
'Do Some Action like a message box so users know what just happened
MsgBox "No-one registered!", vbInformation, "Class Attendance"
End if

Exit_cmdClassAttendance_Click:
Exit Sub

 

[Guest]

Hello:

I finally found a way to get the report to print only those students who had
an attend_status of "registered" [as opposed to canceled].

The following did NOT work, it printed every student who was originally
registered for the class, even those who canceled.

strWhere = "[ClassNo]= '" & Me.ClassNo & "' And
IsNull(Forms![frmClasses]![sfrmClassesqrytblLink]![Attend_Status])"

For the life of me, I don't know why, but the following did exactly what we
wanted. It printed the names of those students who had an Attend_Status of
"registered" and it did not print the names of those students who had an
Attend_Status of "canceled".

strWhere = "[ClassNo]= '" & Me.ClassNo & "' And [Attend_Status]= 'Registered'"

Robert

 

[Guest]

Hello:

This what I get for rushing, I pasted in the wrong "incorrect" expression in
the previous message, sorry about that. Even though I don't understand why
the previous attempt didn't work, here's the final solution.

Hello:

I finally found a way to get the report to print only those students who had
an attend_status of "registered" [as opposed to canceled].

The following did NOT work, it printed every student who was originally
registered for the class, even those who canceled.

strWhere = "[ClassNo]= '" & Me.ClassNo & "' And
[Forms]![frmClasses[![sfrmqrytblClasses]![Attend_Status]= 'Registered'"

For the life of me, I don't know why, but the following did exactly what we
wanted. It printed the names of those students who had an Attend_Status of
"registered" and it did not print the names of those students who had an
Attend_Status of "canceled".

strWhere = "[ClassNo]= '" & Me.ClassNo & "' And [Attend_Status]= 'Registered'"

Robert

 

[Gina Whipp]

I think I know why.. because I made the assumption the field was on a
subform but obviously it was not. Glad you got it working despite my
misinformation.

Gina Whipp

 

[Guest]

Hi Gina:

The field [Attend_Status] is in a subform. However, somewhere during this
problem solving session, I added the [Attend_Status] field to the query.

Robert