TOP(1) in update query?

[Jesper Fjølner]

I'm having problems with "top(1)" part of this SQL. Can I use the TOP word
in an update-query?

UPDATE TOP(1) tblLog SET exittime = #" & Time & "# WHERE username = '" &
CurrentUser & "'"

Running this with db.execute

 

[Douglas J. Steele]

No, you can't.

If you're trying to set the value for a specific row in tblLog, you're going
to have to determine some way of identifying that specific row.

 

[Jesper Fjølner]

If you're trying to set the value for a specific row in tblLog, you're
going

to have to determine some way of identifying that specific row.

I'll figure it out another way. Thanks!

 

[John Spencer (MVP)]

How about something similar to the following untested string.

"UPDATE tblLog SET exittime = #" & Time & "# " & _
"WHERE tblLog.PrimaryKey in " & _
"(SELECT Top 1 A.PrimaryKey FROM tblLog " & _
" WHERE Username = '" & CurrentUser & "' " & _
" ORDER BY PrimaryKey)"

You need to set the order by clause to reflect which fields determine which
record should be the first record.