Export data to Excel using date range

Question

Hi I use the following code to open a report, by entering the dates in the txtStartDate field and the txtEndDate field.

 Dim stDocName As String

     Dim strWhere As String

    strWhere = "[Date] between #" & Format(Me.txtStartDate, "dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"

    stDocName = "rptSouthW"

    DoCmd.OpenReport stDocName, acPreview, , strWhere

    DoCmd.RunCommand acCmdZoom100

How do I get the following code below to work in the same way as the code above so that I only have to end the dates once, the reason I ask is I have several reports to export to Excel and its a pain to keep entering the date range.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryClaimsDetail", "P:\Incident Report.xls", , "Claim Detail"

Thanks

Bob

Answer 1

Marshall

The following code works fine.

Between Forms!frmReportPrinter.txtStartDate AND Forms!frmReportPrinter.txtEndDate

Thank you everyone for your help.

Regards,

Bob

Answer 2

The " and & are messed up:

Between "#" & Format(Forms!frmReportPrinter.txtStartDate, "dd-mmm-yy") & "#" AND "#" & Format(Forms!frmReportPrinter.txtEndDate, "dd-mmm-yy") & "#"

But why format a date value to a date literal.  You can just use:

Between Forms!frmReportPrinter.txtStartDate AND Forms!frmReportPrinter.txtEndDate

Answer 3

Let's try it his way:

1. Make your [Date] field as- MyDate:[Format([Date],"dd-mmm-yy")
2. Make Criteria line as- Between Format(frmReportPrinter!txtStartDate, "dd-mmm-yy") AND  Format(frmReportPrinter!txtEndDate, "dd-mmm-yy")
3. Make sure your form frmReportPrinter is opened and both txtStartDate and txtEndtDate have data
4. Run your query

Nadia

Answer 4

Hi Nadia

When I enter the following in the Date field of the qry I get the following error "The expression you have entered has an invalid date value"

Between #" & Format(frmReportPrinter!txtStartDate, "dd-mmm-yy") & "# AND #" & Format(frmReportPrinter!txtEndDate, "dd-mmm-yy") & "#"

The Date field is formated to a medium date dd-mmm-yy.

Can you see why I am getting this error?

Thanks for your help.

Bob

Answer 5

Hello Bob,

You can enter

 - Between #" & Format(Form_yourForm!txtStartDate, "dd-mmm-yy") & "# AND #" & Format(Form_yourForm!txtEndDate, "dd-mmm-yy") & "#" -

in the Criteria line for [Date] field in your reports recordsource queries (such as in qryClaimsDetail).

Nadia