Need help with form coding (Date Problems)

[Randy]

This code which I recieved from the access newsgroups works great except
when the data is appended to my table "CheckedOutCertsAllNumbers " The date
is appended as 12/30/1899 no matter what date I enter on my form. I have
read about putting # in quotes around date or something like that....Any
ideas...Thanks...Randy


sub Form_AfterUpdate()
Dim stDocName As String
Dim strSQL As String
Dim NewCertNum As Long
Dim CertBeg As Long
Dim CertEnd As Long
Dim knt As Integer
Dim Msg As String
Dim Response As String
DoCmd.SetWarnings False

If [EndCertNolbl] = 0 Then
stDocName = "SingleCertAppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else


'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl
cnt = 0

' Define message.
Msg = "BegCert # = " & CertBeg & vbCrLf & "EndCert # = " & CertEnd &
vbCrLf
Msg = Msg & "Letter = " & Me.BeginningInitial & vbCrLf & vbCrLf
Msg = Msg & vbCrLf & "Insert Certifcates?"
Style = vbQuestion + vbYesNo + vbDefaultButton2 ' Define buttons.
' Display message.
Response = MsgBox(Msg, Style)
If Response = vbYes Then ' User chose Yes.
For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut,BeginingCertInitial )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & ", "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ", '"
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "', "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & ", '"
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![BeginningInitiallbl] & "'); "

' now do it
CurrentDb.Execute strSQL
cnt = cnt + 1
Next
MsgBox cnt & " new Certs added", vbInformation + vbOKOnly
DoCmd.SetWarnings True
End If
End If
End Sub

 

[Guest]

Hi Randy,

Sorry, I thought I had sent you the correct code; I hate it when the
computer does what I tell it instead of what I want it to do...


Replace the line:

strSQL = strSQL & [Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] &
", '"

with

strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#, '"

which should be all on one line.


Steve