Search Record Base On Date

[Ximay]

Hi,

The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.

Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String

myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy&#34

Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"

Set rstDate = New ADODB.Recordset

With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With

If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date&#34 = Format(myFileDate, "dd-mm-yyyy&#34
rstDate.Update
End If
rstDate.Close
End Sub

Regards,
May

[Jim W]

The problem is problem in the
"myFileDate=#27-10-2000 16:00#"

Your system settings are likely to be using mm-dd-yyyy. It's trying to convert the date to month of 27, and sql doesn't like that.

You can test it by changing the statement to
"myFileDate=#10-27-2000 16:00#"
and seeing if it works.


------------
Ximay at 10/27/00 5:52:59 AM

Hi,

The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.

Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String

myFileDate=#27-10-2000 16:00#
myDate = Format(myFileDate, "mm-dd-yyyy&#34

Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"

Set rstDate = New ADODB.Recordset

With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With

If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date&#34 = Format(myFileDate, "dd-mm-yyyy&#34
rstDate.Update
End If
rstDate.Close
End Sub

Regards,
May