Date not changing in table

[renenger]

I have this code that a member helped me create in one of my forms. However, the date that is being input into the table is 30 Dec 99. It won't change no matter what the code is changed to.

The date is showing up correctly in the subform, it is just not inputting right into the table.

Private Sub Form_Open(Cancel As Integer)
Dim intNQ
intNQ = Nz(DMax("QuizNo", "tblQuizzes", "logon='" & Environ("Username") & "'"), 0) + 1

Me.subrmQuizzes.Form.Logon = Environ("Username")
Me.subrmQuizzes.Form.DATEControl = Format(Now(), "DD MMM YYYY")
Me.subrmQuizzes.Form.QuizNo = intNQ

CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Format(Now(), "DD/MM/YYYY") & " , '" & Environ("Username") & "'"
End Sub

Any ideas?

 

[Bat17]

What datatype is your field [date] set to in the table?

Peter

 

[renenger]

Date/Time no formatting.

 

[Bat17]

You are passing it a text string, try
CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Now() & " , '" & Environ("Username") & "'"
End Sub

HTH

Peter

Edited to add back an &

 

[renenger]

When I pasted

CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Now() & " , '" & Environ("Username") & "'"

in I get Run Time error 3075: Syntax error (missing operator) in query expression '06/08/2005 10:59:19 AM'.

 

[renenger]

Ok. I changed it from NOW() to Date and it goes through with no problem. However, it still shows in the tbl as 30 Dec 99. I can't figure it out. It shows correctly in the unbound field on the subfrm. \


CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Date & " , '" & Environ("Username") & "'"

 

[renenger]

Ok. I figured it out. Here is what I changed the code to in case anyone else has this same problem:

CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , " & Format$(Date, "yyyy-mm-dd") & ", '" & Environ("Username") & "'"

 

[renenger]

Ok. I Lied. I Thought That Did It But It Made It 1905 And Depending On How I Arrange The Mm/dd/yy, I Get All Different Dates.

I Could Really Use Some Help On This!!!

 

[raskew]

You're in Montana. The default US date format is Short Date (mm/dd/yyyy). The system expects to receive in that order and I expect that's why you're going the mysterious dates. Give it a try and see if it helps.

Bob

 

[neileg]

You have a field called DATE. This may have nothing to do with your current problem but it's a bad idea to use a reserved word as a name for a database object.

 

[Bat17]

oops I forgot the date delimeters!

try

CurrentDb.Execute "INSERT INTO tblQuizzes ( QUIZNO, [DATE], LOGON )SELECT " & intNQ & " , #" & Now() & "# , '" & Environ("Username") & "'"

which wont work in the UK but should in the US

Peter