Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

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,
Bevo
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...", _
                                            Flags:=ahtOFN_HIDEREADONLY)
                                            
    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
    Else
        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

0
Bevos
Asked:
Bevos
1 Solution
 
Patrick MatthewsCommented:
Bevos,

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


Patrick
0
 
BevosAuthor Commented:
Perfect!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now