Sql Date Error

[Jimgarry]

Hi, thanks in advance

Im getting this error when I attemp to run my insert sql "

Run-Time error '12147217900(80040e14);
[Pervasive][Odbc Client Interface][LNA][Pervaiive][ODBC Engine Interface]Invalid date, time or timestamp value.

I get this when I attempt to bring in a date from a record soruce. if I leave the data filed out the sql runs fine.

here is the sql:

Dim cn As Adodb.Connection
Dim rsInvoice As Adodb.Recordset

Dim cnPerv As Adodb.Connection
Dim rsPerv As Adodb.Recordset
Set cnPerv = New Adodb.Connection
Set rsPerv = New Adodb.Recordset
Set cn = New Adodb.Connection
Set rsInvoice = New Adodb.Recordset


Dim strSql As String
Dim strCNString As String
Dim strPervCnString As String
Dim cmdObj As New Adodb.Command
Dim rs1cnt As Long

strCNString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=master;Data Source=ecisqlgram"
cn.Open strCNString
cnPerv.Open "DSN=AcctEmp"

rsInvoice.Open "select * from strong.dbo.tbl_Invoices where accounting = ('" & Date & "') order by fullcompanycd", cn
rsInvoice.MoveFirst


Do Until rsInvoice.EOF


strSql = "Insert into araft(Invoice_no, ...docTot, crMemo_Flg, Inv_Date) values ('" & rsInvoice.Fields...& "'" & rsInvoice.Fields("Invoicetotal&quot & "', '" & rsInvoice.Fields("credit&quot & "','" & rsInvoice.Fields("InvoiceDate&quot & "')"



cmdObj.ActiveConnection = cnPerv
cmdObj.CommandText = strSql
cmdObj.Execute

rsInvoice.MoveNext

rs1cnt = rs1cnt + 1
Loop

Thanks for any help on this
Jim Garry

MsgBox "Finished " & rs1cnt & " Records Imported"


End Sub

Thanks for any help

 

[zemp]

See thread709-726548.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."