 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / September 2006Need Help Adding a 2nd Where Condition
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 Oooooooooops....... forgot to mention that both ClassNo and the child field, Attend_Status, are both TEXT fields. Ooooopsss, forgot to mention that both ClassNo and the child table field, Attend_Status, are TEXT fields. 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 > Hello: > [quoted text clipped - 51 lines] > Thanks, > Robert 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])" 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 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 > Hi Gina: > [quoted text clipped - 9 lines] > > Robert 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 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 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 > Hello: > [quoted text clipped - 25 lines] > > Robert 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.