Date string and Charater string syntax

[Guest]

I need some help on setup up a syntax string for finding UserID and Log date
and blank Time field.

Here is my code

Dim rs As DAO.Recordset
Dim strLogDate As Date
Dim strFind As String
strLogDate = Date
strUserId = Me.UserID
strFind = "UserId = '" & strUserId & "'" {need the correct date and null
string added here}

'open login table
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogIntracking", dbOpenDynaset)
With rs
.MoveLast
'find login record
.FindFirst strFind
If .NoMatch Then
MsgBox "No records found"
Exit Function
Else
MsgBox "Records found"
End If
'add log time
.Edit
!LogOutTime = Time
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing


thank you
ih

 

[Dirk Goldgar]

I need some help on setup up a syntax string for finding UserID and
Log date and blank Time field.

Here is my code

Dim rs As DAO.Recordset
Dim strLogDate As Date
Dim strFind As String
strLogDate = Date
strUserId = Me.UserID
strFind = "UserId = '" & strUserId & "'" {need the correct date
and null string added here}

'open login table
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogIntracking", dbOpenDynaset)
With rs
.MoveLast
'find login record
.FindFirst strFind
If .NoMatch Then
MsgBox "No records found"
Exit Function
Else
MsgBox "Records found"
End If
'add log time
.Edit
!LogOutTime = Time
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing


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 -----

 

[Guest]

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

 

[Dirk Goldgar]

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:

'----- 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 -----

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.

 

[Guest]

Thank You, All is running fine.

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:

'----- 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 -----

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)