INsert Into Access Problem

I cannot figure this out for the life of me. I have an Access database that has a table I am trying to insert a new row to using the Connection Objects Execute command here's what I have:

Dim sql As String
sql = "Insert into RoomAssign" _
& "(RoomNo,Day,PersonNo,TimeIn,TimeOut) " _
& "Values('" & mRoomNo & "','" & mDay & "'," & mPersonNumber _
& ",'" & mTimeIn & "','" & mTimeOut & "')"
dbConn.Execute sql
In the table, RoomNo and Day as strings, PersonNo is a long and TimeIn and TimeOut are Date/Time fields. When I run it, I get this error:
Run-time error '-2147217900 (80040e14)':
Syntax Error in INSERT INTO statement.

If anyone can help, I'd appreciate it much.

dbhutt

The datetime fields are delimited by the # sign, not the '.
Try that.

Dim sql As String
sql = "Insert into RoomAssign" _
& "(RoomNo,Day,PersonNo,TimeIn,TimeOut) " _
& "Values('" & mRoomNo & "','" & mDay & "'," & mPersonNumber _
& ",#" & mTimeIn & "#,#" & mTimeOut & "#)"
dbConn.Execute sql


David Paulson

That's what I thought of originally as well. I tried that and it still doesn't work.

so if anyone has any idea why this doesn't work:

Dim sql as string
sql = "Insert into RoomAssign" _
& "(RoomNo,Day,PersonNo,TimeIn,TimeOut) " _
& "Values('" & mRoomNo & "','" & mDay & "'," & mPersonNumber _
& ",#" & mTimeIn & "#,#" & mTimeOut & "#)"

dbConn.BeginTrans
dbConn.Execute sql



I'd appreciate it much.
dbhutt