ADO,Access, & Dates

[Randy]

I am using a DSN Less connection to an Access database. Getting information
from users through combo and text boxes. When my form opens a text box is
populated with the system time. When I try to send the informaiton to my
database I get a runtime error of -2147217900 (80040e14).
There is a problem with the date field that I cannot figure out. I have
changed the date format in Access and in my VB environment and still cannot
get the date in my database. My code to send the data to the database is
below. Does anybody have any suggestions?
To get the date I set my variable = to Date as follows

datDate = Date


Thanks in advance

Private Sub cmdSendData_Click()

Dim statement As String


statement = "INSERT INTO MeterChangeOut " & _
"(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, Date)
" & _
" Values (" & _
"'" & lblSerialNumText.Caption & "', " & _
"'" & lblUserIDText.Caption & "', " & _
"'" & lblComputerNameText.Caption & "', " & _
"'" & txtReading.Text & "', " & _
"'" & cboLocation.Text & "', " & _
"'" & txtMapNumber.Text & "', " & _
"'" & txtDate.Text & "'" & _
")"


cnnMeterChangeOut.Execute statement, , adCmdText

[Kevin MacCallum]

Couple of things:
Don't use quotes around a date field in a SQL statement. Should use "#"
It requires the date to be in mm/dd/yyyy format when used in this manner.

Also - Date is a reserved word in VBA. To indicate it is a field name wrap
it in [] or better yet change the field name something like MyDate.
Here is a revised SQL statement:

statement = "INSERT INTO MeterChangeOut " & _
"(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, [Date])
" & _
" Values (" & _
"'" & lblSerialNumText.Caption & "', " & _
"'" & lblUserIDText.Caption & "', " & _
"'" & lblComputerNameText.Caption & "', " & _
"'" & txtReading.Text & "', " & _
"'" & cboLocation.Text & "', " & _
"'" & txtMapNumber.Text & "', " & _
-> "#" & format$(txtDate.Text,"mm/dd/yyyy") & "#" & _
")"

For more info do a search for "date literals, specifying in SQL statements"
in the Access help file.
It is talking about using dates in a criteria but concept still applies.

Here is an excerpt:

When you specify the criteria argument, date literals must be in U.S. format,
even if you're not using the U.S. version of the Microsoft Jet database engine.
For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96
in the United States. Be sure to enclose your date literals with the number
sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must
use the following SQL statement:

SELECT *
FROM Orders
WHERE ShippedDate = #5/10/96#;

Kevin

"Randy" <reverett@naeci.com> wrote:
>
>I am using a DSN Less connection to an Access database. Getting information
>from users through combo and text boxes. When my form opens a text box
is
>populated with the system time. When I try to send the informaiton to my
>database I get a runtime error of -2147217900 (80040e14).
>There is a problem with the date field that I cannot figure out. I have
>changed the date format in Access and in my VB environment and still cannot
>get the date in my database. My code to send the data to the database is
>below. Does anybody have any suggestions?
>To get the date I set my variable = to Date as follows
>
>datDate = Date
>
>
>Thanks in advance
>
>Private Sub cmdSendData_Click()
>
> Dim statement As String
>
>
> statement = "INSERT INTO MeterChangeOut " & _
> "(SerialNum, UserID, ComputerName, Reading, Location, MapNumber, Date)
>" & _
> " Values (" & _
> "'" & lblSerialNumText.Caption & "', " & _
> "'" & lblUserIDText.Caption & "', " & _
> "'" & lblComputerNameText.Caption & "', " & _
> "'" & txtReading.Text & "', " & _
> "'" & cboLocation.Text & "', " & _
> "'" & txtMapNumber.Text & "', " & _
> "'" & txtDate.Text & "'" & _
> ")"
>
>
> cnnMeterChangeOut.Execute statement, , adCmdText