Where clause in Access VBA with SQL server backend

[pokeymonsc]

I have an access 2002 front end with an ODBC connection to a SQL server back end. I am trying to open my form using the following code:

Dim rs As DAO.Recordset
Dim QDef As QueryDef
Dim tmpID As Integer

If Not IsNull(Int(Me.EmpID.Value)) Then
tmpID = Me.txtEmpID.Value
Set QDef = CurrentDb.CreateQueryDef("", "select empid from dbo_payperiodcharges where empid = " & tmpID & " and payperiod between '10/01/2004' and '9/30/2005'")
Set rs = QDef.OpenRecordset
counter = rs.RecordCount
End If

I am having trouble with the dates between Access and T-SQL. In the above code I get a 'Type Mismatch' at the line "Set rs = QDef.OpenRecordset". If I use # to bracket the dates I get a 'Syntax Error'.

What I'm trying to do is to limit multiyear records to a single year. Hardcoding the dates is just the first step to setting up a parameter form, but I have to get the hardcoded dates to work first

 

[kramerica]

try the following change to your SQL statement:

Between #10/01/2004# and #9/30/2005#


Kramerica

 

[cLFlaVA]

What area does it point to when you use #'s and get a Syntax Error?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[pokeymonsc]

If I use Set QDef = CurrentDb.CreateQueryDef("", "select empid from dbo_payperiodcharges where empid = " & tmpID & " and payperiod between #10/01/2004# and #9/30/2005#'") I get Run Time Error 3075 "Syntax error in string in query error 'empid =10124 and payperiod between#10/01/04# and #09/30/04#".

If I substitute ' for # then the program will fail at the next line Set rs = QDef.OpenRecordset with a Run-Time Error 3464 "Data type mismatch in criteria expression.

 

[cLFlaVA]

ok, it seems like maybe your employee id's in your database aren't integer values.

try

Set QDef = CurrentDb.CreateQueryDef("", "select empid from dbo_payperiodcharges where empid = '" & tmpID & "' and  payperiod between #10/01/2004# and #9/30/2005#'")

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[pokeymonsc]

Appreciate your reply. Maybe I should have first said the original coding works:

If Not IsNull(Int(Me.EmpID.Value)) Then
tmpID = Me.txtEmpID.Value
Set QDef = CurrentDb.CreateQueryDef("", "select empid from dbo_payperiodcharges where empid = " & tmpID & "")
Set rs = QDef.OpenRecordset
counter = rs.RecordCount
End If

So I don't think that the problem is in the employee id fields. It's only when I try to add a DATE to the WHERE clause that I begin to run into trouble. I've checked the table on the SQL Server for 'payperiod' and it is a datetime field.

 

[cLFlaVA]

You have an extra single quote after your second date parameter.


*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[pokeymonsc]

I can't believe that I look and look and looked and still missed the '. Thanks

Curiously I didn't get any errors in code when I ran it, but neither did exclude the dates prior to 10/01/04. I'm getting 2003 data and early 2004. As if the 2nd part of the WHERE clause wasn't working???????????