Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / February 2005

Tip: Looking for answers? Try searching our database.

Date string and Charater string syntax

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
iholder - 18 Feb 2005 16:25 GMT
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 - 18 Feb 2005 17:42 GMT
> 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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.