SQL Statement help

[Chad]

Hello, I have a sample database I had found and wanted to change one thing.
It keeps track of how many days a person had taken a "Vacation" in the
current year. I would like to do a similar thing is to keep track of how many
times an employee had an excused tardy within the past 6 months from the
current day. The statement used to get the vacation totals is:

Public Function GetVacationAndHolidays()
Dim strSql
Dim frm As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set frm = Forms!frmCalender
Set db = CurrentDb

' Vacation YTD
strSql = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSql = strSql & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND
((tblInput.UserID)=" & glngUserID & ") AND
((tblInput.InputText)='Vacation'));"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
frm!txtVacYTD = rs!TotDays
rs.Close
strSql = "'"

Set rs = Nothing
db.Close
Set db = Nothing
End Function


How would I count how many times the excused tardy was used for the past 6
months starting on the current day? Thanks!

 

[John Spencer]

StrSQL="SELECT Count(InputID) as TotDay" & _
" FROM TblInput" & _
" WHERE InputDate Between DateAdd(""m"",-6,Date())" & _
" and Date() " & _
" and InputText =""Excused Tardy"" "

Of course, you could simplify the whole thing by using DCount and
skipping the entire function

DCOUNT("*","TblInput","InputDate Between DateAdd(""m"",-6,Date()) and
Date() and InputText =""Excused Tardy"" ")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Thanks, Chad]

John where would I use the DCount at?

StrSQL="SELECT Count(InputID) as TotDay" & _
" FROM TblInput" & _
" WHERE InputDate Between DateAdd(""m"",-6,Date())" & _
" and Date() " & _
" and InputText =""Excused Tardy"" "

Of course, you could simplify the whole thing by using DCount and
skipping the entire function

DCOUNT("*","TblInput","InputDate Between DateAdd(""m"",-6,Date()) and
Date() and InputText =""Excused Tardy"" ")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello, I have a sample database I had found and wanted to change one thing.
It keeps track of how many days a person had taken a "Vacation" in the
current year. I would like to do a similar thing is to keep track of how many
times an employee had an excused tardy within the past 6 months from the
current day. The statement used to get the vacation totals is:

Public Function GetVacationAndHolidays()
Dim strSql
Dim frm As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set frm = Forms!frmCalender
Set db = CurrentDb

' Vacation YTD
strSql = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSql = strSql & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND
((tblInput.UserID)=" & glngUserID & ") AND
((tblInput.InputText)='Vacation'));"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
frm!txtVacYTD = rs!TotDays
rs.Close
strSql = "'"

Set rs = Nothing
db.Close
Set db = Nothing
End Function


How would I count how many times the excused tardy was used for the past 6
months starting on the current day? Thanks!

 

[John Spencer]

Basically in the same place you would use the function.

Instead of, for instance
=GetVacationAndHolidays()

You would use (all the following on one line)
=DCOUNT("*","TblInput","InputDate Between DateAdd(""m"",-6,Date()) and
Date() and InputText =""Excused Tardy"" ")

If you wanted to used that multiple places, you could write a very
simple VBA function

Public Function GetTardies()

'All the following on one line
GetTardies = DCOUNT("*","TblInput","InputDate Between
DateAdd(""m"",-6,Date()) and Date() and InputText =""Excused Tardy"" ")

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John where would I use the DCount at?

StrSQL="SELECT Count(InputID) as TotDay" & _
" FROM TblInput" & _
" WHERE InputDate Between DateAdd(""m"",-6,Date())" & _
" and Date() " & _
" and InputText =""Excused Tardy"" "

Of course, you could simplify the whole thing by using DCount and
skipping the entire function

DCOUNT("*","TblInput","InputDate Between DateAdd(""m"",-6,Date()) and
Date() and InputText =""Excused Tardy"" ")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello, I have a sample database I had found and wanted to change one thing.
It keeps track of how many days a person had taken a "Vacation" in the
current year. I would like to do a similar thing is to keep track of how many
times an employee had an excused tardy within the past 6 months from the
current day. The statement used to get the vacation totals is:

Public Function GetVacationAndHolidays()
Dim strSql
Dim frm As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set frm = Forms!frmCalender
Set db = CurrentDb

' Vacation YTD
strSql = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSql = strSql & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & ") AND
((tblInput.UserID)=" & glngUserID & ") AND
((tblInput.InputText)='Vacation'));"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
frm!txtVacYTD = rs!TotDays
rs.Close
strSql = "'"

Set rs = Nothing
db.Close
Set db = Nothing
End Function


How would I count how many times the excused tardy was used for the past 6
months starting on the current day? Thanks!