Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    8

    Date comparison syntax in query criteria

    I'm relatively new to Access and haven't got much programming experience so SQL and VBA are a little out of my league unless it's spoon-fed to me.

    I've got a table with two date fields (Start Date; End Date). I also have a form (frm_dateSelect). On that form are 2 sections, one for Start Dates and the other for End Dates. Each of these two sections has 2 text boxes into which the user inputs a date.

    One textbox ("Before") will cause the associated query (qry_byDate) to return all entries prior to the date provided. The other ("After") will return all entries after the date provided. If both textboxes receive input, the query returns all dates between the two provided. Simple enough.

    I can't get this to work, though, no matter how I phrase the criteria. And then there's the issue of making the query work with BOTH the Start Date and End Date fields. That is, if I want the query to return all entries with, say, Start Dates after 01/01/11 AND End Dates after 01/01/12; or Start Dates between 01/01/11 and 01/01/12 AND End Dates between 01/01/12 and 01/01/13.

    Any help you can provide will be greatly appreciated

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    Are you trying to use a parameterized query? That could be impossible for something this dynamic.

    I suggest a VBA procedure that builds filter string. That string could be used as the parameter for filtering a form or report with the WHERE CONDITION of DoCmd.OpenForm (or OpenReport).

    For an example of flexible filtering, review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    A couple of comments:

    If you are early into your database, then using a naming convention that does NOT allow embedded spaces or special characters will save you many syntax errors in future.

    Letting users type in a string to represent a valid Date may be asking for all kinds of typos.

    If you use a text box(es) on a form, you may be getting text values that are not Dates.
    You can have unbound text boxes, give them a General Date format and turn DatePicker property to For Dates
    This will let user create a properly formatted Date (via DatePicker) for use in your sql/vba.

    Good luck.

  4. #4
    Join Date
    Mar 2013
    Posts
    8

    Lightbulb Solution

    Thanks, folks. Sorry it's taken me so long to reply. I did get this to work, but I had to pick up a VBA for Access for Dummies book in order to get it going. Took a while =P But yes, what I ended up doing was checking to see if my date fields were blank, and if not, concatenating the date information to a Variant that would serve as a WHERE clause. This WHERE clause can also contain all the other conditions (FirstName, LastName, ProjectID, etc.). I also added a subform for Preview purposes. The code looks like this:

    Code:
    Private Sub InstantSearch()
    
    
    'Update the record source
        Me.subMainTbl.Form.RecordSource = "SELECT * FROM qrySearch " & BuildFilter
        
    'Requery the subform
        Me.subMainTbl.Requery
    
    End Sub
    '_________________________________________________________________________________
    
    
    Private Function BuildFilter() As Variant
    
    Dim varWhere As Variant: varWhere = Null
    
        If Me.startDateBefore > "" Then
            varWhere = varWhere & "[StartDate] <= #" & Me.startDateBefore & "# And "
        End If
    
    
        If Me.startDateAfter > "" Then
            varWhere = varWhere & "[StartDate] >= #" & Me.startDateAfter & "# And "
        End If
    
    
        If Me.endDateBefore > "" Then
            varWhere = varWhere & "[EndDate] <= #" & Me.endDateBefore & "# And "
        End If
    
    
        If Me.endDateAfter > "" Then
            varWhere = varWhere & "[EndDate] >= #" & Me.endDateAfter & "# And "
        End If
    
        If IsNull(varWhere) Then        'Do nothing
        Else
            'Tidy Main Filter
            If Right(varWhere, 5) = " And " Then
                varWhere = Left(varWhere, Len(varWhere) - 5)
                varWhere = "WHERE " & varWhere
            End If
        End If
    
        BuildFilter = varWhere
        
    End Function

    Many thanks to Gromit, who provided the skeleton that got me started. http://www.access-programmers.co.uk/...ad.php?t=99353

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

Similar Threads

  1. RC Notation and Date Comparison
    By mkc80 in forum Access
    Replies: 2
    Last Post: 10-10-2012, 06:22 PM
  2. Replies: 3
    Last Post: 08-21-2012, 03:05 PM
  3. Short date comparison
    By andy101 in forum Programming
    Replies: 2
    Last Post: 03-17-2011, 04:36 AM
  4. Replies: 3
    Last Post: 10-15-2010, 11:17 AM
  5. using FindFirst with a Date Criteria (syntax error)
    By ajetrumpet in forum Programming
    Replies: 2
    Last Post: 09-10-2010, 11:01 PM

Tags for this Thread

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