Date comparison syntax in query criteria

[SyntaxSocialist]

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

[June7]

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

[orange]

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.

[SyntaxSocialist]

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:

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