Adding a date filter to an docmd.transfertext command in VBA

Hello everyone, I am working with a form that has a command for exporting a csv file from some data in a table.  I currently have two combo boxes which can filter the export based on user selection in these fields, but I would like to add another option where the user can select a date range to export.  Each record has a [Date Modified] field in the form of MM/DD/YYYY.  

The code I'm currently using is attached.  Please let me know what you would recommend for creating this filter by date options.

Thanks for taking time to comment,
Option Compare Database
Dim fst As String

Private Sub cmdExport_Click()

    Dim strFilter As String
    Dim strSaveFileName As String

    On Error Resume Next
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
    strSaveFileName = ahtCommonFileOpenSave(Filter:=strFilter, _
                                            OpenFile:=False, _
                                            DialogTitle:="Please select filename and location to save your file...", _
    Call UpdateExport_EndNote
    DoCmd.TransferText acExportDelim, "Spec1", "Export_EndNote", strSaveFileName, True

End Sub

Sub UpdateExport_EndNote()
    Dim strSql As String
    strSql = "SELECT Imported.[Reference Type], Imported.Author, Imported.Year, Imported.Title, Imported.[Secondary Author], Imported.[Secondary Title], Imported.[Place Published], Imported.Publisher, Imported.Volume, Imported.[Number of Volumes], Imported.Number, Imported.Pages, Imported.Section, Imported.[Tertiary Author], Imported.[Tertiary Title], Imported.Edition, Imported.Date, Imported.[Type of Work], Imported.[Subsidiary Author], Imported.[Short Title], Imported.[Alternate Title], Imported.[ISBN/ISSN], Imported.[Original Publication], Imported.[Reprint Edition], Imported.[Reviewed Item], Imported.[Custom 1], Imported.[Custom 2], Imported.[Custom 3], Imported.[Custom 4], Imported.[Custom 5], Imported.[Custom 6], Imported.[Accession Number], Imported.[Call Number], Imported.Label, Imported.Abstract, Imported.Notes, Imported.URL, Imported.[Author Address], Imported.Caption " & _
             "FROM Imported " & _
             "WHERE 1=1"
    If Nz(Me!cmbFilter1, "ALL") <> "ALL" Then
        strSql = strSql & " AND [Reprint Edition] = '" & Me!cmbFilter1 & "'"
        If Nz(Me!cmbFilter2, "ALL") <> "ALL" Then
            strSql = strSql & " OR [Reprint Edition] = '" & Me!cmbFilter2 & "'"
        End If
        If Nz(Me!cmbFilter2, "ALL") <> "ALL" Then
            strSql = strSql & " AND [Reprint Edition] = '" & Me!cmbFilter2 & "'"
        End If
    End If
    Call ChangeQDef("Export_EndNote", strSql)

End Sub

Open in new window

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:

Assuming that if your date range fields are null then you effectively want all dates, then add something like this near the end:

    strSql = strSql & " AND (Imported.[Date Modified] Between #" & _
        Nz(Me!StartDate, #1/1/1900#) & "# And #" & Nz(Me!EndDate, #12/31/9999#) & "#)"

Open in new window

BevosAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.