Inserting current DateTime into Audit table

Question

I am in the process of implementing an audit log to record a brief description of changes to the database. My audits table consists of an autonumber PK, empID(number), Description(memo), and auditDate(date/time). My empID and description are being properly inserted without throwing errors, but my date/time is not being inserted. I am thinking this may not be as simple as misplaced quotation marks. My VBA code is as follows: in the afterInsert event: ``` Dim strQuery As String 'Dim js As Integer Dim currDateTime As Date currDateTime = Now() strQuery = "INSERT INTO Audits ([emp Number], Description, dateofAudit) VALUES (" & Me.empID & ", '" & "insertion" & "'," & currDateTime & " )" CurrentDb.Execute (strQuery) ``` Like I said, I can get the first three values in fine, but when I attempt to insert the date time, I run into problems. Any input is appreciated. Hopefully this is not as simple as misplaced quotation marks, as I tried about 4 variations of quotation mark placement before sub…

Accepted Answer

Try it this way.

```
strQuery = "INSERT INTO Audits ([emp Number], [Description], dateofAudit)" & _
    vbCrLf & "VALUES (" & Me.empID & ", 'insertion', Now())"
```

Also give yourself an opportunity to examine the finished text string.

```
Debug.Print strQuery 
```

With that approach, you wouldn't need your `currDateTime` variable. The Date/Time value would be determined when the db engine evaluates the `Now()` function ... the time at which the `INSERT` statement is executed.

If you want the time as per your original approach, format `currDateTime` and add `#` delimiters.

```
strQuery = "INSERT INTO Audits ([emp Number], [Description], dateofAudit)" & _
    vbCrLf & "VALUES (" & Me.empID & ", 'insertion', " & _
    Format(currDateTime, "\#yyyy-mm-dd hh:nn:ss\#") & ")"
```

Inserting current DateTime into Audit table

Solution

Problem