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

Kilted

New member
Local time
Today, 04:42
Joined
Sep 8, 2020
Messages
4
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?

Code:
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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 04:42
Joined
Sep 21, 2011
Messages
7,511
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
Code:
stReportCriteria = stDateField & " Between #" & stStartDate & "# And #" & stEndDate & "#"

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

namliam

The Mailman - AWF VIP
Local time
Today, 05:42
Joined
Aug 11, 2003
Messages
11,692
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

New member
Local time
Today, 04:42
Joined
Sep 8, 2020
Messages
4
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:

Code:
  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

Enthusiastic Amateur
Local time
Today, 04:42
Joined
Sep 21, 2011
Messages
7,511
Your start date is already in USA formt, but you need to do the same for enddate?
Sorry, missed that.
Code:
stEndDate = Format(Date + 1,"mm/dd/yyyy")
 

Kilted

New member
Local time
Today, 04:42
Joined
Sep 8, 2020
Messages
4
Your start date is already in USA formt, but you need to do the same for enddate?
Sorry, missed that.
Code:
stEndDate = Format(Date + 1,"mm/dd/yyyy")

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

Code:
  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:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName, "rptProjectTimesbyDate.xls", True
 

Kilted

New member
Local time
Today, 04:42
Joined
Sep 8, 2020
Messages
4
Hi got it working with:

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

Thanks again :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:42
Joined
Sep 21, 2011
Messages
7,511
Nice one, I was in the middle of a reply, that I have never exported a report to Excel, just data.
 

Users who are viewing this thread

Top Bottom