Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2004, 03:24 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default Passing Date Parameter

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

 
Old April 23rd, 2004, 03:51 PM
Friend of Wrox
Points: 1,035, Level: 12
Points: 1,035, Level: 12 Points: 1,035, Level: 12 Points: 1,035, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

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


 
Old April 28th, 2004, 05:16 AM
Authorized User
 
Join Date: Aug 2003
Location: , , Belgium.
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Greetz

Tom.
 
Old April 28th, 2004, 07:40 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The problem lies here:

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

It should look something like this:

Code:
rstTemp.Filter = "[strField1] = '" & strFilter1 & _
   "' AND [dtmField2] = #" & dtmFilter2 & _
   "# AND [strField3] = '" & strFilter3 & "'"
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
 
Old April 28th, 2004, 03:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Alameda, ca, USA.
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing Input Date Parameter values preethig Crystal Reports 0 February 28th, 2008 08:39 AM
passing parameter sarah lee ASP.NET 1.0 and 1.1 Basics 3 September 5th, 2006 04:29 PM
Passing a Date Parameter ego_mouse BOOK: Professional Crystal Reports for VS.NET 1 January 27th, 2005 08:12 AM
passing parameter.. suzila VB.NET 2002/2003 Basics 7 May 17th, 2004 08:28 PM
Passing Parameter tgopal Javascript 3 August 7th, 2003 07:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.