Array without trailing 0

[Ruegen]

I have an array that is dynamically updated with IDs however once I get to the end there is a trailing blank record (0)

how would I avoid attaching the last record?

ReDim MOArrSchool(1 To 1)

Do Until rs.EOF


   If isABooker(MODateFrom, rs!NewSchoolsID, MODateExclude) = True Then
   Debug.Print "i is at: " & i
   i = i + 1
   MOArrSchool(UBound(MOArrSchool)) = rs!NewSchoolsID
   ReDim Preserve MOArrSchool(1 To UBound(MOArrSchool) + 1) As Long
   PossibleEmails = PossibleEmails + 1
   Debug.Print rs!SchoolName & " ID: " & MOArrSchool(i) & " true " & (PossibleEmails - 1) & " " & rs!NewSchoolsID & vbNewLine
   
   
   'build Array of schools
   
   
   End If

[Ruegen]

solved, carried the i after I append not before sorry

scratch that

even when I change that it still gives me a trailing record (0) value

still need help

[Ruegen]

This is all of my code

Public Sub list1Schools(SchoolType As Integer, AreaSelect As Integer, Enrollment As Integer, dateFrom As Date)


Debug.Print "School Type: " & SchoolType
Debug.Print "Area: " & AreaSelect
Debug.Print "Enrollment: " & Enrollment


Dim PossibleEmails As Long, i As Long
Dim strSQL As String
Dim rs As Recordset
Dim rsBook As Recordset


strSQL = "SELECT tblSchools.NewSchoolsID, tblSchools.SchoolTypeID, tblSchools.AreaID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolSuburb, tblSchools.Removed, tblSchools.Enrollment, tblTourOrganiser.MergedNameT, tblTourOrganiser.SPTEmail, tblTourOrganiser.[ExtNo#], tblTourOrganiser.Signiature" & vbCrLf & _
"FROM tblTourOrganiser RIGHT JOIN (tblAreas INNER JOIN tblSchools ON tblAreas.AreasID = tblSchools.AreaID) ON tblTourOrganiser.TourOrganiserID = tblAreas.TourOrganiserID" & vbCrLf & _
"WHERE (((tblSchools.SchoolTypeID)=" & SchoolType & ") AND ((tblSchools.AreaID)=" & AreaSelect & ") AND ((tblSchools.Removed) Is Null) AND ((tblSchools.Enrollment)>" & Enrollment & "));"


Set rs = CurrentDb.OpenRecordset(strSQL)


    If rs.RecordCount = 0 Then
    Debug.Print "Number of schools: none"
    Exit Sub
    End If






'START SEARCH 1
rs.MoveFirst
rs.MoveLast
rs.MoveFirst
ReDim MOArrSchool(1 To 1)


Do Until rs.EOF


   If isABooker(MODateFrom, rs!NewSchoolsID, MODateExclude) = True Then
   
   MOArrSchool(UBound(MOArrSchool)) = rs!NewSchoolsID
   ReDim Preserve MOArrSchool(1 To (UBound(MOArrSchool) + 1)) As Long
   
   PossibleEmails = PossibleEmails + 1
   
   'build Array of schools
   
   End If


rs.MoveNext
Loop
ReDim Preserve MOArrSchool(1 To (UBound(MOArrSchool) - 1)) As Long
Debug.Print "Schools in the " & DLookup("Area", "tblAreas", "AreasID=" & AreaSelect) & " that can book: " & PossibleEmails




For lc = 1 To UBound(MOArrSchool())


Debug.Print "School: " & DLookup("SchoolName", "tblSchools", "NewSchoolsID=" & MOArrSchool(lc))


Next

I know it's not correct because the array should start at 0 and I am also removing 1 which I would prefer was cleaner.