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

Posted on 2011-05-02
Last Modified: 2012-05-11
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

Question by:Bevos
    LVL 92

    Accepted Solution


    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


    Author Closing Comment


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now