〇シート上「Ｂ(2)」列の最終データ行取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
〇フォーム内の複数コントロールを配列みたく扱う
For i = 1 To 8
Controls(“OptionButton” & CStr(i)).Enabled = False
Next
〇EXCELを閉じる
Application.DisplayAlerts = False
Application.Quit
〇ブックを閉じる
Set wb = ThisWorkbook
Call wb.Close(SaveChanges:=False)
〇商と剰余
5 \ 2 = 2 (“\”は円マーク)
5 Mod 2 = 3
〇ビットシフト(もどき)
右へ５ビット tag = tag \ 2^5 (“\”は円マーク)
左へ５ビット tag = tag * 2^5
〇SQL-1
SELECT COUNT(MS_AbsenceDay.ID) AS Cnt, MS_AbsenceDay.IndexID, MS_AbsenceDay.FisYear, Sys_StudentsMast.Name, Sys_StudentsIndex.Grade, Sys_StudentsIndex.Class,
Sys_StudentsIndex.Number, Sys_StudentsIndex.Flag, Sys_StudentsMast.PaPa, Sys_StudentsMast.Family, Sys_StudentsMast.Phon, Sys_StudentsMast.Address_A,
Sys_StudentsMast.Address_B, Sys_StudentsMast.MaMa, Sys_StudentsMast.BirthDate, Sys_StudentsMast.GobAddress
FROM MS_AbsenceDay INNER JOIN
Sys_StudentsIndex ON MS_AbsenceDay.ScCode = Sys_StudentsIndex.ScCode AND MS_AbsenceDay.IndexID = Sys_StudentsIndex.IndexID AND
MS_AbsenceDay.FisYear = Sys_StudentsIndex.FisYear INNER JOIN
Sys_StudentsMast ON MS_AbsenceDay.ScCode = Sys_StudentsMast.ScCode AND MS_AbsenceDay.IndexID = Sys_StudentsMast.IndexID
WHERE (MS_AbsenceDay.ScCode = ”) AND (MS_AbsenceDay.DayIndex >= ) AND (MS_AbsenceDay.DayIndex <= ) AND
(LEFT(MS_AbsenceDay.AbsData, 1) = 'C' OR
LEFT(MS_AbsenceDay.AbsData, 1) = 'D')
GROUP BY MS_AbsenceDay.IndexID, MS_AbsenceDay.FisYear, Sys_StudentsMast.Name, Sys_StudentsIndex.Grade, Sys_StudentsIndex.Class, Sys_StudentsIndex.Number,
Sys_StudentsIndex.Flag, Sys_StudentsMast.PaPa, Sys_StudentsMast.Family, Sys_StudentsMast.Phon, Sys_StudentsMast.Address_A, Sys_StudentsMast.Address_B,
Sys_StudentsMast.MaMa, Sys_StudentsMast.BirthDate, Sys_StudentsMast.GobAddress
HAVING (COUNT(*) > 5)
ORDER BY Sys_StudentsIndex.Grade, Sys_StudentsIndex.Class, Sys_StudentsIndex.Number
〇SQL-2
‘
‘欠席数が７以上を‥は､意外とヤネコイ
‘学年・学級・月始・月末 を指定して該当者のIndexIDを取得､シートに書込む
‘次にそのIndexIDをキーとして､生徒マスタを読む
‘
SqlStr = “SELECT Sys_StudentsIndex.IndexID, Sys_StudentsIndex.StuNumber, Cnt FROM (SELECT Sys_StudentsIndex.IndexID, Sys_StudentsIndex.StuNumber, COUNT(Sys_StudentsIndex.ID ) as Cnt FROM Sys_StudentsIndex ”
SqlStr = SqlStr & ” INNER JOIN MS_AbsenceDay ON Sys_StudentsIndex.FisYear = MS_AbsenceDay.FisYear AND Sys_StudentsIndex.IndexID = MS_AbsenceDay.IndexID ”
SqlStr = SqlStr & ” WHERE (Sys_StudentsIndex.Grade = ” & grStr & “) AND (Sys_StudentsIndex.Class = ” & clStr
SqlStr = SqlStr & ” ) AND ( ” & mnTopStr & ” <= MS_AbsenceDay.DayIndex) AND (MS_AbsenceDay.DayIndex <= " & mnEndstr
SqlStr = SqlStr & " ) AND (( Left(MS_AbsenceDay.AbsData ,1) = 'C') or (Left(MS_AbsenceDay.AbsData ,1) = 'D')) "
SqlStr = SqlStr & " GROUP BY Sys_StudentsIndex.IndexID, Sys_StudentsIndex.StuNumber ORDER By Sys_StudentsIndex.StuNumber) WHERE 1 <= Cnt "
Set Rec = DbInfo.AdCon.Execute(SqlStr)