Date Error in SQL Statement 1

[Jimgarry]

Hi, I posted this in pervasive also but Im hopping someone herer can shed light on this error:

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

 

[zemp]

I have found that in order to insert a date into Pervasive (2000i was the database I used) I had to format the date as below,

Format(myDateVar, "YYYY-MM-DD")

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."

 

[Jimgarry]

zemp, it was so simple. Thank you very much. It worked just like it should.

thanks for your help
Jim