Solved Generating a report between two dates - vba (1 Viewer)

[Kilted]

Hi,

I have an issue which I can't resolve so would like some advice.

I'd like to generate a report between two dates in the [Date Worked] field passed as criteria to the report. Those dates are from 30th June (what ever year we're in) to today's date. I'd then like to export it to Excel automatically but one step at a time.

When I click the button a parameter field box opens and asks me 'stDateField' and throws a 3071 error (expression typed incorrectly or too complex).

Can anyone help me out please?

Private Sub cmdExportReport_Click()

    Dim stDocName As String
    Dim stStartDate As String
    Dim stEndDate As String
    Dim todaysYear As Integer
    Dim stReportCriteria As String
    Dim stDateField As String

    stDocName = "rptProjectTimesbyDate"
    stEndDate = Date + 1
    stDateField = "[Date Worked]"
   
    todaysYear = Year(Date)
    stStartDate = DateSerial(todaysYear, 30, 6)
    stStartDate = Format(stStartDate, "mm/dd/yyyy")

    stReportCriteria = "[stDateField] Between #" & stStartDate & "# And #" & stEndDate & "#"
   
    Debug.Print stReportCriteria
   
    DoCmd.OpenReport stDocName, acPreview, , stReportCriteria

End Sub

 

[Gasman]

I don't understand why you don't just use [Date Worked] in your criteria.?
What is the benefit of what you are doing now? it is just complicating matters?

However you would need to concatenate it like you have for the date variables.?

Try

stReportCriteria = stDateField & " Between #" & stStartDate & "# And #" & stEndDate & "#"

FWIW I would Debug.Print stReportCriteria which would show you your error.?

 

[namliam]

stDateField as your statement is, is expected as a field inside your report.

I am guess your field inside your report is "date worked" << PS will ignore the naming problem.
Try:
stReportCriteria = "[Date Worked] Between #" & stStartDate & "# And #" & stEndDate & "#"

Or.... maybe as you intended
stReportCriteria = stDateField & " Between #" & stStartDate & "# And #" & stEndDate & "#"

 

[Kilted]

Thanks for your replies.
I've been staring at this for a while and possibly got muddled up a little.
It doesn't throw an error with:

  stReportCriteria = "[Date Worked] Between #" & stStartDate & "# And #" & stEndDate & "#"

but it doesn't generate the report based on the dates given. The report has dates outwith the criteria. I narrowed the dates as a test.
Debug.Print stReportCriteria gives:

[Date Worked] Between #01/09/2020# And #12/09/2020#

Oh, do I need to change to US dates?

 

[Gasman]

Your start date is already in USA formt, but you need to do the same for enddate?
Sorry, missed that.

stEndDate = Format(Date + 1,"mm/dd/yyyy")

 

[Kilted]

Your start date is already in USA formt, but you need to do the same for enddate?
Sorry, missed that.

stEndDate = Format(Date + 1,"mm/dd/yyyy")

Hi Gasman, no the stStartDate is UK date too. First of September. Changed it to this:

  stReportCriteria = "[Date Worked] Between #" & Format(stStartDate, "mm/dd/yyyy") & "# And #" & Format(stEndDate, "mm/dd/yyyy") & "#"

and all is working.

Thanks for your help

Could you point me in the direction for exporting the report to excel (in the same directory) and overwriting the file without prompt?

I'm thinking something like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName, "rptProjectTimesbyDate.xls", True

 

[Kilted]

Hi got it working with:

DoCmd.OutputTo acOutputReport, "rptProjectTimesbyDate", acFormatXLS, "S:\xx\xx\rptProjectTimesbyDate.xls"

Thanks again

 

[Gasman]

Nice one, I was in the middle of a reply, that I have never exported a report to Excel, just data.