Can someone please help me debug my code. I am trying to pass a date to a function which uses this date and filters the recordset. I am using an ACCESS database where I read the record then I pass the date and filter a SQL Server 2000 database. I am getting a type mysmatch error. I am passing three parameters. The reason I suspected the Date field to the problem is because I tested passing just the two fields and it worked fine. As soon as I added the date field back I started getting a type mismatch error message. I remembered that you should be putting "#" before and after the datefield but I forgot how to do it with SQL Server. Anyway, here is my code.


Dim mysql As String

Set rsDD_Data = New ADODB.Recordset
mysql = "SELECT * FROM DD_Data"
rsDD_Data.CursorType = adOpenDynamic
rsDD_Data.LockType = adLockOptimistic
rsDD_Data.Open mysql, adoConnection

Set rsCom_Meeting = New ADODB.Recordset
mysql = "SELECT * FROM Committee_Meeting_Attachment WHERE Processed = 0"
rsCom_Meeting.CursorType = adOpenDynamic
rsCom_Meeting.LockType = adLockOptimistic
rsCom_Meeting.Open mysql, adoBenConn

'Update ACCESS Table Committee_Meeting_Attachment Processed Column 'from SQL Server DD_Data Table

If Not rsCom_Meeting.EOF Then
    Dim strSSN As String
    Dim dtMeeting_Date As Date
    Dim strExhibit_Name As String
    Dim rsTmp_data As Recordset

    strSSN = rsCom_Meeting!SSN
    dtMeeting_Date = rsCom_Meeting!meeting_date
    strExhibit_Name = rsCom_Meeting!Exhibit_Name
    Set rsTmp_data = FilterRecordset(rsDD_Data, "SSN", "Meeting_Date", _
         "Exhibit_Name", strSSN, dtMeeting_Date, strExhibit_Name)
End If


Thanks,
Judy

I guess I should explain further. I am using an ADO connection. What I am trying to do is read the ACCESS table first then search the SQL Server table with the date field from the ACCESS table. After getting the matching record from the SQL Server table, I will then update the ACCESS table. Below, I forgot to include the function which I pass the parameters to filter the SQL Server table with the matching ACCESS record.


Public Function FilterRecordset(rstTemp As ADODB.Recordset, strField1 As String, strField2 As Date, strField3 As String, _
strFilter1 As String, strFilter2 As Date, strFilter3 As String) As ADODB.Recordset

' Set a filter on the specified Recordset object and then
' open a new Recordset object.

rstTemp.Filter = strField1 & " = '" & strFilter1 & "' AND " & strField2 & " = #" & strFilter2 & "# AND " & strField3 & "= '" & strFilter3 & "'"

Set FilterRecordset = rstTemp

End Function

I do not know if this helps but i think you have to declare strField2 as a string in your function

Greetz

Tom.

The problem lies here:

rstTemp.Filter = strField1 & " = '" & strFilter1 & "' AND " & strField2 & " = #" & strFilter2 & "# AND " & strField3 & "= '" & strFilter3 & "'"

It should look something like this:

Or in general:

rstTemp.Filter = "[Text Field] = '" & Passed Argument & "'"
rstTemp.Filter = "[Datet Field] = #" & Passed Argument & "#"
rstTemp.Filter = "[Numeric Field] = " & Passed Argument

You have:

rstTemp.Filter = Passed Argument & " = '" & Passed Argument & "'"

which makes no sense. Also, as a side note, if a field is a date, don't prefix it with "str". Prefix it with "dtm". Therefore, in your case strField2 would be dtmField2. It keeps you from type mismatch problems when you code by cluing you in to use # instead of '.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Judy,

in the definition of FilterRecordset, the third parameter (strField2) is a date (I agree with Greg that the str prefix was a poor choice) but when you call it

    Set rsTmp_data = FilterRecordset(rsDD_Data, "SSN", "Meeting_Date", _
         "Exhibit_Name", strSSN, dtMeeting_Date, strExhibit_Name)

the third parameter ("Meeting_Date") is a string. This is causing a type mismatch.

Marco