Results 1 to 3 of 3
  1. #1
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25

    Recordset Search

    Hi,


    Am having problems with date search in recordset. I am trying to extract data from a table filed depending if it is between two dates.
    With code below the recordset populates with the correct records and the do while loop steps through the records but the if statement is not working to select records within the date range.
    The code below is only the part that is not working.
    Const strJetDate = "\#mm\/dd\/yyyy\#" is at the top of the code.

    Dim tbAnnual As DAO.Database
    Dim rstAnnual As DAO.Recordset
    Dim rstAnnualsearch As DAO.Recordset
    Set tbAnnual = CurrentDb

    ADayCount = 0
    strFindDate1 = Format(txAtADate, strJetDate)
    strFindDate2 = Format(strAEndDate, strJetDate)

    Set rstAnnual = tbAnnual.OpenRecordset("tbAnnual", dbOpenDynaset)
    rstAnnual.Filter = "ANameIndex = '" & strEmpId & " '"
    Set rstAnnualsearch = rstAnnual.OpenRecordset()
    With rstAnnualsearch
    Do While Not .EOF
    If !AStartDate >= " & strFindDate1 & " And !AStartDate <= " & strFindDate2 & " Then
    ADayCount = ADayCount + !ADays
    End If
    .MoveNext
    Loop
    End With

  2. #2
    Join Date
    May 2010
    Posts
    339
    Hi ColPat,

    You know this could be accomplished with a simple calendar popup and two textboxes right?
    You wouldn't have to rely on complex VBA code.

  3. #3
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25
    Thanks for the idea. Will try to work out how to do it that way. One of the dates I am using is from another table and the end date is from a pop-up calendar.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ADO Recordset.Open (SQL) does nothing
    By workindan in forum Programming
    Replies: 3
    Last Post: 06-23-2010, 02:07 PM
  2. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  3. This Recordset Is Not Updateable
    By botts121 in forum Access
    Replies: 1
    Last Post: 11-11-2009, 05:43 PM
  4. The Recordset is not updatable
    By bullet_proof302 in forum Access
    Replies: 2
    Last Post: 11-07-2009, 12:13 PM
  5. ADO.Recordset - Write
    By Marvin in forum Programming
    Replies: 0
    Last Post: 07-17-2009, 07:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums