SQL INSERT and dates

[Dean Murray]

I am using VB5 with an Access 97 database and I wanted to insert a record into a table with numerous date fields. I used something similar to the following syntax

dbs.Execute " INSERT INTO Employees
FirstName, LastName, DateDue VALUES '" & strFname & "' , '" & strLname & "', #01/05/00# "

works fine but when I try to substitute the hard coded date value with a variable it fails. I don't get a error msg (I am trapping both VB and Jet errors); just no data is entered into the database. If I remove the date part the record is entered. I have tried using a variable as a string, as a date, and as a variant with the # concatenated and with it not concatenated. I used every possible combination I could think of. The funny thing is that what works fine for the UPDATE SET statement doesn't for the INSERT INTO.
To complete the code I ended up using a recordset but it seems silly to create a recordset just to enter a single record.
Any thoughts out there? Is it a bug or is the bug with the programmer?
Thanks




[This message has been edited by Dean Murray (edited 01-17-2000).]

[Clunietp]

This code works fine for me:

    Dim db As Database
    
    Set db = DBEngine.OpenDatabase("Biblio.mdb")
    
    db.Execute "Insert Into TestNameDate (Name, TheDate) VALUES ('TOM', #" & Date & "#)"
    
    db.Close

don't forget the parenthesis around the fields and values

Tom

[Dean Murray]

Thanks. I thought I tried that but maybe that is the one combination I didn't do. I'll give it a shot tonight.