How Export Between two date range data to Excel through CmdButton?

[rayhan]

I want to export between two date range data in Excel Sheet .
I want only Specific date range data export to excel through a button.
Two text field in my form and one button.
one txtfield for record start date other txtField for Record end date.
and one button.
Please help me.

 

[pbaldy]

Try creating a query that uses the two textboxes in the criteria, and then TransferSpreadsheet or OutputTo.

 

[ashleedawg]

I think you want to export the records from Access to Excel based on dates on an Access Form? Here are a couple options:

You could create a (permanent) query, for example called "qryDateRange" with and in SQL View paste:

SELECT * 
FROM myTable 
WHERE myTable.myDate BETWEEN [forms]![MyForm].[txtDateFrom] And [forms]![MyForm].[txtDateTo]));

and then call it from a Command Button etc with:

DoCmd.TransferSpreadsheet acExport, , "qryDateRange", "C:\MyFolder\FileName.XLSX", True

Or, same idea but with a temporary query:

Sub ExportFromDateRange()
    Const xlsFileName = "C:\MyFolder\MyExcelExport.XLSX"
    Const xlsTabName = "My_XLS_Tab_Name"
    Const tmpQueryName = "_qTemp"
    Dim sql As String, qdf As QueryDef
    
    On Error Resume Next 'don't give an error on next line
    DoCmd.DeleteObject acQuery, tmpQueryName 'delete temp query
    On Error GoTo 0 'resume normal error handling
    sql = "SELECT * from Table1 WHERE myDate BETWEEN #" & dateFrom & "# AND #" & dateTo & "#"
    Set qdf = CurrentDb.CreateQueryDef(tmpQueryName, sql) 'create temp query
    DoCmd.TransferSpreadsheet acExport, , tmpQueryName, xlsFileName, True 'export to Excel
    DoCmd.DeleteObject acQuery, tmpQueryName 'delete temp query
    MsgBox "Exported to: " & xlsFileName
End Sub

I didn't add error-handling; for example you could add something to verify that there are actually dates on the form:

If Not IsDate(Forms("Form1")!dateFrom) Or Not IsDate(Forms("Form1")!dateTo) _
    Or Forms("Form1")!dateTo < Forms("Form1")!dateFrom Then
    MsgBox "Invalid Date"
    Exit Sub
End If