SQL Error

Good day,

I am getting an "Too few parameters error:Expected 1" on the indicated line below:

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset

Dim strsql1 As String
Dim strsql2 As String
Dim strsql3 As String

Dim intExistV As Integer, intExistF As Integer, intTBAvail As Integer, dblForeAb As Double, intTrueAvail As Integer, dblThresh As Double

Set db = CurrentDb()

strsql1 = "Select Count([empid]) as CountofEmpID from tblvacation where [tblvacation]![curtour] = " & Me.curtour
strsql2 = "Select count([empid]) as CountofTourEmps from tblEmployee where tblEmployee.curtour = " & Me.curtour
strsql3 = "Select Count([empid]) as CountofEmpIDF from tblfloater where tblfloater.curtour = " & Me.curtour


Set rs1 = db.OpenRecordset(strsql1) <--Error on this line
Set rs2 = db.OpenRecordset(strsql2)
Set rs3 = db.OpenRecordset(strsql3)

Just wondering if anyone my know what is causing this?
I know the syntax is correct and the remaining parameters are optional.

Thanks

Syntax incorrect-- can't use "!" here, use "."

strsql1 = "Select Count([empid]) as CountofEmpID from tblvacation where [tblvacation].[curtour] = " & Me.curtour

Only other possible problem is that you have either your table name or field name spelled wrong.

S-

Thanks for the reply S. But still getting the error. I have double checked the table/field names and they are correct.

Let me know if you have any other suggustions!

Are you sure that

[tblvacation].[curtour]

is a numeric field and not text

S-

It is a text field.
Would I have to make any changes to the code?

Help

text variables need ' around it

strsql1 = "Select Count([empid]) as CountofEmpID from tblvacation where [tblvacation].[curtour] = '" & Me.curtour & "'"


S-

Hey Again,

I want to make an addition to the statement:

strsql1 = "Select Count([empid]) as Countofempid from tblvacation where tblvacation.curtour = '" & Me.curtour & "'"

Basically it will be a where cause like this

Where tblvaction.vdate = Me.vdate

Do anyone know if it is possible to add that to the statement above.

Thanks

strsql1 = "Select Count([empid]) as Countofempid from tblvacation where tblvacation.curtour = '" & Me.curtour & "' and tblvaction.vdate = #" & Me.vdate & "#;"


S-

Hey I hate to trouble again,

But I am getting the error
"Too few parameters error:Expected 1"

It is showing up on the indicated error line:

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset

Dim strsql1 As String
Dim strsql2 As String
Dim strsql3 As String

Dim intExistV As Integer, intExistF As Integer, intTBAvail As Integer, dblForeAb As Double, intTrueAvail As Integer, dblThresh As Double
Dim RetVal As String

Set db = CurrentDb()

strsql1 = "Select Count([empid]) as Countofempid from tblvacation where tblvacation.curtour = '" & Me.curtour & "' and tblvaction.vdate = #" & Me.txtvdate & "#;"
strsql2 = "Select count([empid]) as CountofTourEmps from tblEmployee where tblEmployee.curtour = '" & Me.curtour & "'"
strsql3 = "Select Count([empid]) as CountofEmpIDF from tblfloater where tblfloater.curtour = '" & Me.curtour & "'"


Set rs1 = db.OpenRecordset(strsql1) <---Error is here
Set rs2 = db.OpenRecordset(strsql2)
Set rs3 = db.OpenRecordset(strsql3)
rs1.MoveFirst
intExistV = rs1![CountofEmpID]
rs2.MoveFirst
intTBAvail = rs2![CountofTourEmps]
rs3.MoveFirst
intExistF = rs3![CountofEmpIDF]
rs1.Close
rs2.Close
rs3.Close

dblForeAb = intTBAvail * 0.1
intTrueAvail = intTBAvail - dblForeAb
dblThresh = intTrueAvail * 0.04

Note: In my previous post I indicated Me.vdate but the correct name of the textbox is txtvdate.
Also the field txtvdate is Unbound.

Thanks Again

I guess I should have asked before

tblvaction.vdate is a date field, right?

S-

sure is.

In you SQL statement tblvacation is spelled wrong

S-

Crazy! I have been here for looking @ the dam statement for an hour. Working Now. Thanks.
Do it still matter that txtvdate is a date field?

I assumed it was a date because I had you put #aroundit#

strings need '
dates need #

S-