Date

[Dan]

I am trying to insert the date into a table but all it
will insert is 12/30/1989 and when you click on the value
it changes to 12:00:10AM. I am using the folling code:

Dim dateSent As Date
Dim dateDue As Date

strSQL = "INSERT INTO [Bills] ([MemberNumber],
[BillNumber], ..., [dateSent], [dateDue], ...) " & _
"VALUES(" & Me.MemberNumber & ", " & billNumber & ", "
& ... & ", " & Date & ", " & dateDue & ", " & ... & ")"

CurrentDb.Execute strSQL, dbFailOnError

The dateSent is the current date and the dateDue is a
value that is stored in another table. The dateSent and
dateDue from the Bills table (which is where I am trying
to insert the values) are formated to have the short
dates fromat. The value of the date due is comming from a
form that has the correct date displayed on it and it is
linked to a query. I did noticed that I had been setting
the dateSent and DateDue values to 0 so I don't know if
that could have messed things up

 

[Allen Browne]

There are two issues here:
1) Making sure Access knows the text boxes are dates;
2) The date in the SQL string must be formatted and delimited.

1. If the text boxes are bound to a date/time field, Access knows they are
dates. If they are unbound, set their Format property to Short Date or
similar.

2. Use # as the delimiter, and format American:
"INSERT INTO ... #" & Format([dueDate], "mm/dd/yyyy") & "# ...