I have an after udpate event that is supposed to write the updated value to
an audit log. The code does update the correct record, but it updates it to
a time field. For example, the updated record should read 8/1/2007, instead
it reads 12/30/1899. If I select that field, the value shows as 12:05:44 AM.

Here is the code:
Private Sub dtmCCSContractSigned_AfterUpdate()

Dim NewVal As Date

NewVal = Me.dtmCCSContractSigned

strSQL = "UPDATE tAuditLogDt SET dtmNewVal= " & NewVal & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLogDt WHERE txtNTName='" &
fOSUserName() & "')"

DoCmd.RunSQL strSQL
End Sub

Any thoughts?

PJ

Date/time values need to be delimited with # characters (and need to be in a
format Access will recognize, since it doesn't respect regional settings).

Try:

Private Sub dtmCCSContractSigned_AfterUpdate()

Dim NewVal As Date

NewVal = Me.dtmCCSContractSigned

strSQL = "UPDATE tAuditLogDt SET dtmNewVal= " & _
Format(NewVal, "\#yyyy\-mm\-dd hh\:nn\:ss\#) & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLogDt WHERE txtNTName='" &
fOSUserName() & "')"

DoCmd.RunSQL strSQL
End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"PJFry" <(E-Mail Removed)> wrote in message
news:5C025F7D-F2E8-45FB-B12D-(E-Mail Removed)...
>I have an after udpate event that is supposed to write the updated value to
> an audit log. The code does update the correct record, but it updates it
> to
> a time field. For example, the updated record should read 8/1/2007,
> instead
> it reads 12/30/1899. If I select that field, the value shows as 12:05:44
> AM.
>
> Here is the code:
> Private Sub dtmCCSContractSigned_AfterUpdate()
>
> Dim NewVal As Date
>
> NewVal = Me.dtmCCSContractSigned
>
> strSQL = "UPDATE tAuditLogDt SET dtmNewVal= " & NewVal & _
> " WHERE anID=(SELECT Max(anID) FROM tAuditLogDt WHERE txtNTName='" &
> fOSUserName() & "')"
>
> DoCmd.RunSQL strSQL
> End Sub
>
> Any thoughts?
>
> PJ