VBA SQL statement help

Hello, I am trying to figure out how to write a sql statement that will count
how many times excused absence was used within the past 6 months starting on
todays date. I wrote the statement below but it doesnt give me any results?
What am I missing? Thanks!

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

Set frm = Forms!frmCalendar
Set db = CurrentDb

'TEST
strSql = "SELECT Count(tblInput.InputID) AS TotDays FROM tblInput "
strSql = strSql & "WHERE tblInput.InputDate = >DateAdd('M',-6,Date())
And tblInput.UserID = 2 AND ((tblInput.InputText)='Excused Absence';"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
frm!txtTEST = rs!TotDays
rs.Close
strSql = "'"


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



--
Thanks,
Chad

Personally, I'd use DCOUNT to do this. It is optimized for this type of
thing, and requires a lot less code.

Public Function GetExcusedAbsense(intUserID) as integer

Dim strCriteria as string

strCriteria = "[InputDate] >= #" & DateAdd('M',-6,Date()) & "# AND " _
& "[UserID] = " & intUserID & " AND " _
& "[InputText]='Excused Absence'"
GetExcusedAbsense = DCOUNT("InputID", "tblInput", strCriteria)

End Function

If you insist on using your code, then you neen to move the DateAdd( )
function outside of the quotes, and set it off with # symbols (as I did
above). Other than that, I think your code looks about right.

HTH
Dale



"SoggyCashew" <SoggyCashew.TakeThisOut@discussions.microsoft.com> wrote in message
news:EA2A5369-9A4B-4F07-9C83-2787617A9355@microsoft.com...
> Hello, I am trying to figure out how to write a sql statement that will
> count
> how many times excused absence was used within the past 6 months starting
> on
> todays date. I wrote the statement below but it doesnt give me any
> results?
> What am I missing? Thanks!
>
> Public Function GetVacationAndHolidays()
> Dim strSql
> Dim frm As Form
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> Set frm = Forms!frmCalendar
> Set db = CurrentDb
>
> 'TEST
> strSql = "SELECT Count(tblInput.InputID) AS TotDays FROM tblInput "
> strSql = strSql & "WHERE tblInput.InputDate = >DateAdd('M',-6,Date())
> And tblInput.UserID = 2 AND ((tblInput.InputText)='Excused Absence';"
> Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
> frm!txtTEST = rs!TotDays
> rs.Close
> strSql = "'"
>
>
> Set rs = Nothing
> db.Close
> Set db = Nothing
> End Function
>
>
>
> --
> Thanks,
> Chad