Exclude grouped Schools if one of their bookings is unconfirmed

[Ruegen]

You mean 3 variables? Concatenate strings or variables, basically same (6 of 1, 1/2 dozen of another).

Could modify query with QueryDefs, no idea how that pertains to your situation.

Good Luck on this, I am way lost Ruegen.

This is the function I ended up with

Function strQueryIN(DateFrom As Date, DateUntil As Date) As String


Dim str1 As String, str2 As String, str3 As String


'confirmed
str1 = "SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),""Fix Email"","""") AS Email, IsValidEmail([SchoolEmail]) AS Expr1 " & vbCrLf & _
"FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN (tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID " & vbCrLf & _
"WHERE (((tblBookings.StatusID)=4) AND ((tblBookings.BookingDate) Between #" & DateFrom & "# And #" & DateUntil & "#) AND ((tblShows.TheatreShow)=False)) " & vbCrLf & _
"GROUP BY tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),""Fix Email"",""""), IsValidEmail([SchoolEmail]) " & vbCrLf & _
"HAVING (((tblSchools.NewSchoolsID)<>9389)) " & vbCrLf & _
"ORDER BY tblSchools.SchoolName;"


'not confirmed
str2 = "SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),""Fix Email"","""") AS Email " & vbCrLf & _
"FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN (tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID " & vbCrLf & _
"WHERE (((IIf([statusid]=2 Or [statusid]=3 Or [statusid]=6 Or [statusid]=7 Or [statusid]=11 Or [statusid]=12,1,0))=1) AND ((tblBookings.BookingDate) Between #" & DateFrom & "# And #" & DateUntil & "#) AND ((tblShows.TheatreShow)=False)) " & vbCrLf & _
"GROUP BY tblSchools.NewSchoolsID, tblSchools.SchoolName, IIf(IsNull([SchoolEmail]) Or (IsValidEmail([SchoolEmail])=False),""Fix Email"","""") " & vbCrLf & _
"HAVING (((tblSchools.NewSchoolsID)<>9389)) " & vbCrLf & _
"ORDER BY tblSchools.SchoolName;"


Dim db As DAO.Database
Set db = CurrentDb


Dim que1 As DAO.QueryDef, que2 As DAO.QueryDef, sque As DAO.QueryDef


For Each sque In db.QueryDefs
If sque.Name = "qry_Y_Confirm" Then
CurrentDb.QueryDefs.delete "qry_Y_Confirm"
End If


If sque.Name = "qry_N_Confirm" Then
CurrentDb.QueryDefs.delete "qry_N_Confirm"
End If


Next


Set que1 = CurrentDb.CreateQueryDef("qry_Y_Confirm", str1)
Set que2 = CurrentDb.CreateQueryDef("qry_N_Confirm", str2)




strQueryIN = "SELECT qry_Y_Confirm.NewSchoolsID, qry_Y_Confirm.SchoolName, qry_N_Confirm.Email " & vbCrLf & _
"FROM qry_Y_Confirm LEFT JOIN qry_N_Confirm ON qry_Y_Confirm.NewSchoolsID = qry_N_Confirm.NewSchoolsID " & vbCrLf & _
"WHERE (((qry_Y_Confirm.NewSchoolsID)<>9389) AND ((qry_N_Confirm.NewSchoolsID) Is Null)) " & vbCrLf & _
"ORDER BY qry_Y_Confirm.SchoolName;"


que1.Close
que2.Close




Set que1 = Nothing
Set que2 = Nothing
Set sque = Nothing




End Function

It's not perfect - but it does work. I'd perfer it if it didn't create the query def and kept it all in the object (virtually) rather than creating a saved query. That way I wouldn't have to delete it either and save some code.

So far I have it saving as a string function and it updates a list box. However I would like to store it all in one public variable.

[Ruegen]

The function works fine - I am able to delete the 3 saved queries (still would like a way of never creating them even with query def).

Thanks for your patience June.