VBA SQL statement help

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

SoggyCashew

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
 
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
 
Dale, Im going to have to go with what I got because I have several other
text boxes with diferent names other than "Excused Absence" that im using in
this function. Im not sure I know how to do what your saying or telling me
how to fix the one I have? I tried the SQL below with no luck. It outlines
the line and sets focus to the 'M'

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

--
Thanks,
Chad


Dale Fye said:
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
Click to expand...
 
The operator is ">=" and not "= >".

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' "



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

strSql = "SELECT Count(InputID) AS TotDays " _
& "FROM tblInput " _
& "WHERE InputDate >= #" & DateAdd("m",-6,Date()) & "# " _
& " AND UserID = 2 " _
& " AND InputText= 'Excused Absence' "

Dale
 
Back
Top