VBA Query.. (1 Viewer)

[skiphooper]

HI Everyone.

I changed the following function, and can't get it to work at all.

Public Function GetRangedays()
Dim strSQL
Dim frm As Form
Dim db As Database
Dim rs As Recordset

Set frm = Forms!frmCalender
Set db = CurrentDb

' Vacation
' The next line was changed , adding between startingdate
' and endingdate
' syntax error
' _____________________________________
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE (([tblInput]![InputDate] Between " & "([forms]![frmChoose]![StartingDate])" & " AND ([forms]![frmchoose]![EndingDate])" & " AND ([tblInput]![UserID]=" & glngUserID & ") AND ((tblInput.InputText = 'Vacation'));"
' _______________________________________________

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacTot = rs!TotDays
rs.Close
strSQL = "'"

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

Thanks in Advance

Skip

 

[Drevlin]

Try this:

strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE (([tblInput]![InputDate] Between "
strSQL = strSQL & "[forms]![frmChoose]![StartingDate] "
strSQL = strSQL & "AND [forms]![frmchoose]![EndingDate]) "
strSQL = strSQL & "AND ([tblInput]![UserID]= "
strSQL = strSQL & glngUserID & ") AND (tblInput.InputText = 'Vacation'));"

 

[Drevlin]

To help you out in the future: it's always easiest to build your query in query design mode and then convert the SQL to your code. That way you don't have to mess with trying to figure out the parenthesis.

 

[skiphooper]

HI Drevlin,

Thanks for the reply.

Here is the code I now have, with the following error.
Too few parameters. Expected 2.

I've looked at it but still can't get the statement right.

Public Function GetRangedays()
Dim strSQL
Dim frm As Form
Dim db As Database
Dim rs As Recordset

Set frm = Forms!frmCalender
Set db = CurrentDb
frm!txtVacTot = ""
' Vacation
strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((([tblInput]![InputDate] Between "
strSQL = strSQL & "[forms]![frmChoose]![StartingDate] "
strSQL = strSQL & "AND [forms]![frmchoose]![EndingDate])) "
strSQL = strSQL & "AND ([tblInput]![UserID]= " & glngUserID & ") "
strSQL = strSQL & "AND ((tblInput.InputText = 'Vacation')));"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
frm!txtVacTot = rs!TotDays
rs.Close
strSQL = "'"


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

Thanks Again
Skip

 

[Drevlin]

Ok... threw me for a loop there for a second...

Take the calls to your form's text boxes out of the quotes:

strSQL = "SELECT Count(InputID) AS TotDays FROM tblInput "
strSQL = strSQL & "WHERE ((([tblInput]![InputDate] Between #"
strSQL = strSQL & [forms]![frmChoose]![StartingDate]
strSQL = strSQL & "# AND #" & [forms]![frmchoose]![EndingDate] & "#)) "
strSQL = strSQL & "AND ([tblInput]![UserID]= " & glngUserID & ") "
strSQL = strSQL & "AND ((tblInput.InputText = 'Vacation')));"

I apologize for not catching that the first time.

Peace

 

[skiphooper]

Hi Drevlin,

Thank you very much.

It works fine now.

Thanks Again

Skip