Problem with updating date field 1

[limpan]

This function below is meant to update a date field.

Public Sub complete(id As Integer, completed As String)
Dim dbs As DATABASE
Set dbs = CurrentDb
Dim completedDate As Date

If completed = "Yes" Then
completed = "No"
dbs.Execute "UPDATE TempHI SET Completed = '" & completed & "' WHERE bestId = " & id & ";"
dbs.Execute "UPDATE orderTable SET completed = '" & completed & "' WHERE bestId = " & id & ";"
Else
completed = "Yes"
completedDate = Date
dbs.Execute "UPDATE TempHI SET Completed = '" & completed & "', " & _
"completedDate = " & completedDate & " WHERE bestId = " & id & ";"
dbs.Execute "UPDATE orderTable SET Completed = '" & completed & "', " & _
"completedDate = " & completedDate & " WHERE bestId = " & id & ";"
End If

dbs.Close
End Sub


The problem is that after running this procedure, the field completed contains the date 1905-05-27 instead of today´s date. Very strange...
Can anyone see the reason to this?

Thanks,
/Linus

 

[jflachman]

Your code is a little confusing, but are you asking about the field [tt]CompletedDate[/tt]? You want it to have today's date?
I think you want to replace your line
[tt]CompletedDate=Date[/tt] (what's Date in this line? where is it defined?)
with
[tt]CompletedDate=Now()[/tt]
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[limpan]

You´re right, it probably is a bit confusing, but you got the general idea right, I want completedDate to have today´s date. I only want to have the date though, and not the time. Can I use the Now() function in a way so that I only get the date?

Linus

 

[tlbroadbent]

Your use of Date is appropriate. The problem is that you need to delimit the date with # in the update statement. Otherwise the date is treated as a numerical expression.

"completedDate = #" & completedDate & "# WHERE bestId = " & id & ";"
Terry

http://www.defendscouting.com/

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[thomasyu]

create a common public function in VBA as follows
' Access always look for date format in m/d/y despite of windows setting
Function AccessDate(ByVal datDate As Date) As String
AccessDate = "#" & Format(datDate, "m/d/yy&quot & "#"
End Function

Change your statement to
dbs.Execute "UPDATE orderTable SET Completed = '" & completed & "', " & _
"completedDate = " & AccessDate(completedDate) & " WHERE bestId = " & id & ";"