Problem with SQL in Module

[ecugrad]

Getting an End of Statement error in my SQL Statement. Can somebody look at and tell where I went wrong..

strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & " CInt(gstrYear) & " )) AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)=('1/2 Vacation Day')));"

I think it's between the year and month.

Thanks
Mike

 

[chrissie1]

strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)=('1/2 Vacation Day')));"

I dont think you need the year because you do the month() function this will only return a value between 1 and 12 "What a wonderfull world" - Louis armstrong

 

[ecugrad]

Chrisse, the code is in a calender and I'm getting 2002 numbers in Vacation, Sick, etc. for January 2003. I need it to read the year to distingush between 2001,2002,2003, etc.. Below is the code I'm using. Thanks

Option Compare Database
Option Explicit

Public glngUserID As Long
Public gstrMonth As String
Public gstrYear As String

Function Cal(m, Y, user As Long)
Dim a
Dim DayOne
Dim gOffset
Dim f As Form
Dim workdate

Set f = Forms!frmCalender

glngUserID = user
gstrMonth = m
gstrYear = Y
f!month.SetFocus
m = f!month
Y = f!year

For a = 1 To 37
f("Day" & a + gOffset).Visible = False
f("Text" & a + gOffset).Visible = False
f("date" & a + gOffset) = Null
f("day" & a + gOffset) = Null
Next

DayOne = DateValue(m & "/1/" & Y)
workdate = DayOne
gOffset = WeekDay(DayOne) - 1

For a = 1 To LenMonth(DayOne)
f("Day" & a + gOffset).Visible = True
f("Text" & a + gOffset).Visible = True
f("date" & a + gOffset) = workdate
workdate = workdate + 1
f("day" & a + gOffset) = a
Next

Call PutInData
GetVacationAndHolidays
End Function

Function LenMonth(d)
Dim start, finish

start = DateValue(month(d) & "/1/" & year(d))
finish = DateAdd("m", 1, start)
LenMonth = finish - start
End Function

Function SendToInputBox(mynum)
Dim f As Form
Dim g As String

Set f = Forms!frmCalender
g = Format(f("Date" & mynum), "dddd mmmm d, yyyy&quot

DoCmd.OpenForm "frmInputBox"

With Forms!frmInputBox
!InputDay = mynum
!InputDate = f("Date" & mynum)
!InputFor = g
!original_text = f("Text" & mynum)
!InputText = f("Text" & mynum)
!InputText.SetFocus
End With

SendKeys "{F2}^{HOME}", False
End Function

Public Sub PutInData()
Dim sql As String
Dim f As Form
Dim Db As Database
Dim rs As Recordset
Dim mynum
Dim i As Integer

Set f = Forms!frmCalender

For i = 1 To 37
f("text" & i) = Null
Next i

sql = "SELECT * FROM tblInput WHERE (((month([InputDate])) = " & f!month & " AND ((Year([InputDate])) = " & f!year & &quot" _
& " And ((UserID) = " & glngUserID & &quot)) ORDER BY InputDate;"
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(sql, dbOpenSnapshot)
If rs.RecordCount > 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
rs.FindFirst "inputdate=#" & f("date" & i) & "#"
If Not rs.NoMatch Then
f("text" & i) = rs!InputText
End If
End If
Next i
End If
End Sub

Public Function GetVacationAndHolidays()
Dim strSQL
Dim frm As Form
Dim Db As Database
Dim rs As Recordset

Set frm = Forms!frmCalender
Set Db = CurrentDb



' Vacation Full Day Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)='Vacation'));"
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacMo = rs!TotDays
rs.Close
strSQL = "'"

' Vacation Full Day Year
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)='Vacation'));"
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacYear = rs!TotDays
rs.Close
strSQL = "'"



' Vacation 1/2 Day Month
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & " & CInt(gstrYear) AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)=('1/2 Vacation Day')));"
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacHalf = rs!TotDays * 0.5 + frm!txtVacMo
rs.Close
strSQL = "'"

' Vacation YTD
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((Year([InputDate])=" & CInt(gstrYear) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)=('1/2 Vacation Day')));"
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacYTD = rs!TotDays * 0.5 + frm!txtVacYear
rs.Close
strSQL = "'"

 

[ajdesalvo]

You have an extra double quote between the cInt(gstrMonth) and cInt(gstrYear):

=" & CInt(gstrMonth) & " CInt(gstrYear) & " ))

Should be:

=" & CInt(gstrMonth) & CInt(gstrYear) & " ))

Also I think there might be another issue. I am assuming that [InputDate] is in a format like 01/13/2003. In this case, Month(01/13/2003) returns the number 1.

You are testing against 2 global variables for the month and year. Therefore, if gstrMonth = 1 and gstrYear = 2003, then you have it WHERE 1 = 12003.

Hope I was of some help.

 

[chrissie1]

strSQL = strSQL & "WHERE ((Month([InputDate])=" & CInt(gstrMonth) & &quot AND (Year([inputdate]) =" & CInt(gstrYear) & &quot AND ((tblInput.UserID)=" & glngUserID & &quot AND ((tblInput.InputText)=('1/2 Vacation Day')));"

try this i found it here

' Vacation 1/2 Day Month

"What a wonderfull world" - Louis armstrong