RunSQL UPDATE Doesn't Work with Time Field

[Guest]

Greetings:

I am trying to update Places and Times of runners in a
database that keeps information on foot races.

But I can't get my UPDATE statement to work correctly
from VBA. Whenever I attempt to update the runner's data
I get the following message:

Run-time error '3144'
Syntax error in UPDATE statement.

Here's the code in question:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = " & Me.Place & _
", Time = " & Me.Time & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"
End Sub

The following UPDATE works fine, so I know it has something to do
with how I attempt to enter the Me.Time field.

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = " & Me.Place & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"
End Sub

I've tried enclosig the Me.Time in single quotes as if it were
a text field and in hash marks, but nothing seems to work and
I always get the same error.

Help!

Regards,

Charles Ritz

 

[Dirk Goldgar]

Greetings:

I am trying to update Places and Times of runners in a
database that keeps information on foot races.

But I can't get my UPDATE statement to work correctly
from VBA. Whenever I attempt to update the runner's data
I get the following message:

Run-time error '3144'
Syntax error in UPDATE statement.

Here's the code in question:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = " & Me.Place & _
", Time = " & Me.Time & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"
End Sub

The following UPDATE works fine, so I know it has something to do
with how I attempt to enter the Me.Time field.

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = " & Me.Place & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"
End Sub

I've tried enclosig the Me.Time in single quotes as if it were
a text field and in hash marks, but nothing seems to work and
I always get the same error.

Help!

Regards,

Charles Ritz

Your problem is primarily with the name of the field: "Time". This is a
reserved word, so you have to enclose it in square brackets. If it's a
date/time field, you also should use the "hash marks" (#) around the
value, so your code would look like this:

DoCmd.RunSQL _
"UPDATE runners SET Place = " & Me.Place & _
", [Time] = #" & Me.Time & "#" & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"