Update SQL Date Issue

[ChrisLayfield]

I am building a password table that when certain criteria are met, the user must change their password. Everything works which is nice, but this update statement goes bad for some reason. I put a code break on the line of code and everything shows the correct information for the variables, but when I proceed the date entered into the table is 12/30/1899 for some reason. Note when I highlight Date on the code break is shows as 7/9/2010. Any ideas?

        db.Execute "UPDATE tbl_EmployeePasswords " _
            & "SET Password = '" & strNewPassword & "'," _
            & " CreationDate = " & Format(Date, "Short Date") & " " _
            & "WHERE  EmployeeID = '" & strUserID & "';"

 

[vbaInet]

Don't save a formatted date. Save just Date.

Use the format function in the query or on the control that will display this date.

 

[ChrisLayfield]

Well that saves the date in the correct format, but still saves the incorrect date (12/30/1899).

 

[vbaInet]

Well that saves the date in the correct format, but still saves the incorrect date (12/30/1899).

I didn't quite follow? Please explain.

And I was referring to changing this:

CreationDate = " & Format(Date, "Short Date")[/code

To this:
[code]CreationDate = " & Date

 

[ChrisLayfield]

Yeah I did that...I should have updated code to show. The date is entered into the table correctly, but the date is incorrect. Still being entered as 12/30/1899 for some reason.

db.Execute "UPDATE tbl_EmployeePasswords " _
            & "SET Password = '" & strNewPassword & "'," _
            & " CreationDate = " & Date & " " _
            & "WHERE  EmployeeID = '" & strUserID & "';"

 

[vbaInet]

This could be an Access bug or corruption. Have you tried Compact & Repair?

What version of Access and Windows are you using?

 

[boblarson]

I believe you'll need date delimiters:

db.Execute "UPDATE tbl_EmployeePasswords " _
            & "SET Password = '" & strNewPassword & "'," _
            & " CreationDate = [COLOR="red"]#[/COLOR]" & Date & "[COLOR="Red"]#[/COLOR] " _
            & "WHERE  EmployeeID = '" & strUserID & "';"

 

[vbaInet]

I believe you'll need date delimiters:

db.Execute "UPDATE tbl_EmployeePasswords " _
            & "SET Password = '" & strNewPassword & "'," _
            & " CreationDate = [COLOR=red]#[/COLOR]" & Date & "[COLOR=Red]#[/COLOR] " _
            & "WHERE  EmployeeID = '" & strUserID & "';"

I didn't think that would matter since Date is passed. Hmmm....

 

[boblarson]

I didn't think that would matter since Date is passed. Hmmm....

It might not, but since it isn't working without them, a test would be to include them - No?

 

[ChrisLayfield]

Thanks Bob, that did it. Still learning all of this syntax stuff, gotten better with my quotation marks though lol.

 

[boblarson]

I didn't think that would matter since Date is passed. Hmmm....

And now that I think about it I think it is because the SQL String actually is a text string and so it just needs to have the little "nudge" to let it know to treat it as a date (and perhaps it isn't in the US date format which is what it really wants to see).

 

[boblarson]

Thanks Bob, that did it. Still learning all of this syntax stuff, gotten better with my quotation marks though lol.

Cool, glad to hear.

 

[vbaInet]

And now that I think about it I think it is because the SQL String actually is a text string and so it just needs to have the little "nudge" to let it know to treat it as a date (and perhaps it isn't in the US date format which is what it really wants to see).

That was what I thought but I thought the text equivalents were usually four digits. Oh wait, I'm thinking of Excel.