Case 1 'Current
strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & "" - "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID, tblStudents.fldDateLeft " & vbCrLf & _
"FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
"WHERE (((tblStudents.fldDateLeft) Is Null Or (tblStudents.fldDateLeft)>Date())) " & vbCrLf & _
"ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"
Case 2 'Left
strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & "" - "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID, tblStudents.fldDateLeft " & vbCrLf & _
"FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
"WHERE (((tblStudents.fldDateLeft)<Date())) " & vbCrLf & _
"ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"
Case 3 'All
strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & "" - "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID " & vbCrLf & _
"FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
"ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"