> I need some help on setup up a syntax string for finding UserID and
> Log date and blank Time field.
[quoted text clipped - 33 lines]
> thank you
> ih
I'm not sure what you're asking with regard to the time field, and you
don't say what field in the table holds the date. Do you have a
LogInDate and a LogOutDate? A LogInTime and a LogOutTime? I'm going to
guess that you have just one date, LogDate, and two times, LogInTime and
LogOutTime, and that you want to update the record that has the current
date as the LogDate and has Null as the LogOutTime. In that case, your
code could be corrected as follows:
'----- start of revised code #1 -----
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLogDate As String '*** NOTE CHANGE ***
Dim strFind As String
strLogDate = Format(Date, "\#mm/dd/yyyy\#")
strUserId = Me.UserID
strFind = _
"UserId = '" & strUserId & "'" & _
" AND LogDate = " & strLogDate & _
" AND LogOutTime Is Null"
'open login table
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogIntracking", dbOpenDynaset)
With rs
'find login record
.FindFirst strFind
If .NoMatch Then
MsgBox "No records found"
Else
MsgBox "Records found"
'add log time
.Edit
!LogOutTime = Time
.Update
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
'----- end of revised code #1 -----
However, this would be more efficient:
'----- start of revised code #2 -----
Dim db As DAO.Database
Dim strLogDate As String
Dim strSQL As String
strLogDate = Format(Date, "\#mm/dd/yyyy\#")
strUserId = Me.UserID
strSQL = _
"UPDATE tblLogInTracking SET LogOutTime = Time() " & _
"WHERE UserId = '" & strUserId & "'" & _
" AND LogDate = " & strLogDate & _
" AND LogOutTime Is Null"
Set db = CurrentDb
With db
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "No records found"
Else
MsgBox "Records found and updated"
End If
End With
Set db = Nothing
'----- end of revised code #2 -----

Signature
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
iholder - 18 Feb 2005 20:45 GMT
Thank you, Dirk
This is exactly what I wanted to do.
I went with you second code. But I am getting an error "Too few parameters.
Expected 1." Maybe a problem with the SQL syntax
> > I need some help on setup up a syntax string for finding UserID and
> > Log date and blank Time field.
[quoted text clipped - 111 lines]
> Set db = Nothing
> '----- end of revised code #2 -----
Dirk Goldgar - 18 Feb 2005 21:39 GMT
> Thank you, Dirk
>
> This is exactly what I wanted to do.
>
> I went with you second code. But I am getting an error "Too few
> parameters. Expected 1." Maybe a problem with the SQL syntax
[...]
>> However, this would be more efficient:
>>
[quoted text clipped - 25 lines]
>> Set db = Nothing
>> '----- end of revised code #2 -----
You'll get that message if the database engine doesn't recognize one of
the names used in the SQL statement. I only guessed at "LogDate" as the
name of the date field; is it "LogInDate", maybe, or some other name
than I guessed? If so, you need to change the name used in building the
SQL statement.
Another possibility would be that you aren't running the query from
Microsoft Access. If you run it from some other application, I don't
think the Time() function will be recognized.

Signature
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
iholder - 18 Feb 2005 22:09 GMT
Thank You, All is running fine.
> > Thank you, Dirk
> >
[quoted text clipped - 43 lines]
> Microsoft Access. If you run it from some other application, I don't
> think the Time() function will be recognized.