vb sql code to Insert Date into a date/time field in Microsoft Access

[Guest_imported]

Can't seem to find an answer for this anywhere.

I am trying to insert a date into a microsoft access 2000 table. The field I am attempting to insert into is a date/time field. I know how to do this with the ado control but I have a specific situation where I need to execute an insert statement with SQL. I have successfully inserted with SQL in all fields but the date/time field. I understand the difference between Update and Insert, and I realize that the primary key must not be null. I have tried to use the CDate functions and other creative solutions but to no avail. How do I mask the date from visual basic so that it will successfully insert/update into access 2000? Here is the code that I am trying to use. Thanks in Advance

txtdate.Text = Date
Dim mstrInsertSQL As String
Dim strDateMask As String

strDateMask = CDate(txtdate.Text)

mstrInsertSQL = "Insert Into MyTable (datefield) Values('" & strDateMask & "')"

Data1.Database.Execute mstrInsertSQL

Any ideas???

 

[MichaelRed]

WEll, this would create a record in the table which has ONLY the one field. If ANY other field in the table has a constraint (Not Null ....) then the Insert will fail.

I also do not believe the word "Values" is useful, but should be replaced with a comma.

You do not explicitly state wheather Data1 is an ADO /RDO/DAO recordset, although the reference to Ms. Access 2K 'suggests' an ADO recordset. If it NOT, then some other syntax issues should be checked.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[SheriLW]

From your Post:

mstrInsertSQL = "Insert Into MyTable (datefield) Values('" & strDateMask & "')"

Access2000 requires the # sign when inserting into a date field.

Dim datDateMask as date

datDateMask = cdate(strdatemask)

mstrInsertSQL = "Insert into MyTable(datfield) _
& " values(#" & datdatemask & "#)

You should have no problems doing it this way.