Date Range on a report??

[smithj14]

I have a form to enter start and end dates then select a worker name to filter a report. This all works fine and when the report is open in preview mode it shows the date range in the txtboxes on the report header, but when I print the report the value for these txtboxes are #Name?

Here is the code
Date Range Form:

Private Sub cmdViewErrorReport_Click()
Dim stDocName As String
Dim stDocName2 As String
Dim stWhere As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "Error Report"
stDocName2 = "Error Report - Date Range"
stLinkCriteria = " [ReviewDate] Between " & CLng(Nz(Me.txtBeginDate, txtEndDate)) & " And " & CLng(Me.txtEndDate)

Me.Filter = stLinkCriteria
Me.FilterOn = Len(stLinkCriteria)

If Not IsNull(Me.cboWorker) Then
stWhere = stWhere & "([Worker] = """ & Me.cboWorker & """)"
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria, stWhere
End If

DoCmd.Close acForm, stDocName2
DoCmd.RunMacro "PrintErrorReport"
DoCmd.Close acReport, "Error Report"
End Sub


The controlsource property of the txtboxes on my report:

txtBeginDate:
=Forms.[Error Report - Date Range].txtBeginDate

txtEndDate:
=Forms.[Error Report - Date Range].txtEndDate

I know the code isn't pretty, I am still testing and have not added error checking yet but all is working except this part.

The only thing I haven't tried is changing the name of the report. Maybe the spaces and dash need to removed?

Help

[nico5038]

Normally I use a form to enter the date(s) and/or additional fields and pass these as the filter parameter when starting the report.
Another option is to have the parameters in the WHERE clause like e.g.
[Begin date: ]
as this will ask for a value when the report is started.

Finally when you have the fields on a report (which effectively should be "output only"), the you would have to refer to:
=Reports.[Error Report - Date Range].txtEndDate

But I wouldn't use input textfields on a report, but on a form...

Nic;o)

[smithj14]

Sorry, I might have not been clear enough. The code I posted was from the click event from a seperate unbound form that has txtboxes to enter dates and a combobox to select the worker then filter the report based on these criteria.

Then the report auto prints and closes displaying all the correctly filtered data on the printed report.

I just need the entered date values from the form to print out on the report.
The txtboxes on the report are not used for input just to display the date values.

If you need more info let me know.

[nico5038]

Strange, as it should work as you code the fields.
Perhaps the formname with the embedded "-" sign causes trouble as some Access versions aren't "happy" with special characters in form and/or field names.

Can you retry this with a form just named like "frmReportStartEnd".

Nic;o)

[youmike]

Nico's advice regarding embedding "-" is good. Access just doesn't like that sort of thing and you get most reliable (and fastest) performance if all object names are restricted to using A-Z (upper and lower case) and 1-9. Using a capital at the start of each word in the name as Nico suggests makes names easier to read.

I'd also be inclined to redesign stWhere so that it doesn't have successive double quotes. I tend to favour passing values of controls to global constants, which in this case would be set via the after update event of the combo box. In this case, you'd declare a string constant, say gcstrWorkerName. I use the prefix gcstr for a global string, because it is helpful when debugging. I also tend to put all such global declarations in a VBA module which I call modConstants.

If you adopt this approach, your code to build stWhere (which would need to be declared as global) would look something like
If Not IsNull(Me.cboWorker) Then
gcstWhere = stWhere & " AND Worker = " & gcstrWorkerName
End if.

Hope this makes sense

[smithj14]

Ok, thank both of you for the point in the right direction.
I now have it working printing the date range on the report. I created a new form and used a different approach to the coding. The code I was using would only work if both dates were entered. Now I can enter a start date, an end date or both, and all works fine. I think my problem was with the selection of the worker. When I added that part of code it did not work again, so I removed it.

Here is the code I used:

Private Sub cmdErrorReport_Click()
Dim stReport As String
Dim stForm As String
Dim stMacro As String
Dim stDateField As String
Dim stWhere As String
Dim lngView As Long
Const stDateFormat = "\#mm\/dd\/yyyy\#"

stReport = "ErrorReport"
stForm = "ErrorDateRange"
stMacro = "PrintErrorReport"
stDateField = "[ReviewDate]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
stWhere = "(" & stDateField & " >= " & Format(Me.txtStartDate, stDateFormat) & ")"
End If

If IsDate(Me.txtEndDate) Then
If stWhere <> vbNullString Then
stWhere = stWhere & " AND "
End If
stWhere = stWhere & "(" & stDateField & " < " & Format(Me.txtEndDate + 1, stDateFormat) & ")"
End If

If CurrentProject.AllReports(stReport).IsLoaded Then
DoCmd.Close acReport, stReport
End If

DoCmd.OpenReport stReport, lngView, , stWhere

DoCmd.RunMacro stMacro
DoCmd.Close acForm, stForm
DoCmd.Close acReport, stReport

End Sub

However i still need to select a worker name from a combo box and have the report filter by the entered dates and the worker name. This part is a must as we view the report based on the workers errors and needs to be specific to each worker.

Can you guys help with this or should I start another post with that part of problem?

[nico5038]

You'll need to code all combinations of dates and worker filled and/or empty.
Would look something like:

Private Sub cmdErrorReport_Click()
 
Dim stWhere As String
 
stWhere = ""
 
If IsDate(Me.txtStartDate) and IsDate(Me.txtEndDate) Then
   stWhere = "([ReviewDate] between #" & Format(Me.txtStartDate, "mm-dd-yyyy") & "and & Format(Me.txtEndDate, "mm-dd-yyyy") & ")"
elseif IsDate(Me.txtStartDate) then
   stWhere = "([ReviewDate] >=" & Format(Me.txtStartDate, "mm-dd-yyyy") & ")"
elseif IsDate(Me.txtEndDate) then
   stWhere = "([ReviewDate] <=" & Format(Me.txtEndDate, "mm-dd-yyyy") & ")"
End If
 
' stWhere filled?
IF len(stWhere) > 0 then
   if len(nz(Me.cmbWorker)) > 0 then
      ' cmbWorker filled and stWhere
      stWhere = stWhere & " AND ([Worker] =" & Me.cmbWorker & ")"
   endif
else
   if len(nz(Me.cmbWorker)) > 0 then
      ' cmbWorker filled and stWhere empty
      stWhere = "([Worker] =" & Me.cmbWorker & ")"
   endif
endif
 
If stWhere <> "" Then
   DoCmd.OpenReport "ErrorReport", acViewPreview, , stWhere
else
   DoCmd.OpenReport "ErrorReport", acViewPreview
endif
 
End Sub
 

Just keep the form opened, thus an erroneous selection can be corrected easily by the user.

Nic;o)

[smithj14]

I am getting there, slowly but surely.

I used some of the code you suggested and added some of my variables.

It filters the report by the worker now. When I enter a date range and select a worker I get the following runtime error:

Run Time Error '3075':
Syntax error (Missing Operator) in query expression '((stDateField between ##09/01/2008# and #09/30/2008#) AND [Worker] = 'Last, First')'

When I enter just a date range and no worker I get the following runtime error:

Run Time Error '3075':
Syntax error (Missing Operator) in query expression '((stDateField between ##09/01/2008# and #09/30/2008#))'

I have the worker names stored in this format:
Column - WorkerName
Date - LastName, FirstName


Here is my code:

Private Sub cmdErrorReport_Click()
Dim stReport As String
Dim stForm As String
Dim stMacro As String
Dim stDateField As String
Dim stWhere As String
Dim stLinkCriteria As String
Dim lngView As Long
Const stDateFormat = "\#mm\/dd\/yyyy\#"

stReport = "ErrorReport"
stForm = "ErrorDateRange"
stMacro = "PrintErrorReport"
stDateField = "[ReviewDate]"
lngView = acViewPreview



If CurrentProject.AllReports(stReport).IsLoaded Then
DoCmd.Close acReport, stReport
End If

stWhere = ""

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
stWhere = "(stDateField between #" & Format(Me.txtStartDate, stDateFormat) & " and " & Format(Me.txtEndDate, stDateFormat) & ")"
ElseIf IsDate(Me.txtStartDate) Then
stWhere = "(stDateField >=" & Format(Me.txtStartDate, stDateFormat) & ")"
ElseIf IsDate(Me.txtEndDate) Then
stWhere = "(stDateField <=" & Format(Me.txtEndDate, stDateFormat) & ")"
End If

If Len(stWhere) > 0 Then
If Len(Nz(Me.cboWorkerName)) > 0 Then
stWhere = stWhere & " AND [WORKER] =" & "'" & Me.cboWorkerName & "'"
End If
Else
If Len(Nz(Me.cboWorkerName)) > 0 Then
stWhere = "[Worker]=" & "'" & Me.cboWorkerName & "'"
End If
End If

If stWhere <> "" Then
DoCmd.OpenReport stReport, lngView, , stWhere
'DoCmd.RunMacro stMacro
'DoCmd.Close acForm, stForm
'DoCmd.Close acReport, stReport
Else
MsgBox "You must enter a worker and date range for this report!", vbOKOnly, "Not enough information!"
End If
End Sub


Thank you in advance. Especially if it is a dumb mistake on my part.

[nico5038]

There's an additional "#" before the first date, remove it from the stWhere filling.

Nic;o)

[smithj14]

Ok I just about have it.

If I enter just start date, I get reports from then on.
If I enter just end date, I get reports from then back.
If I select just worker name, I get reports for that worker.

Only problem is they don't all work together. If I enter a worker and the wrong date range the report still opens with all fields saying error.

Here is the latest code:

Private Sub cmdErrorReport_Click()





Dim stReport As String
Dim stForm As String
Dim stMacro As String
Dim stDateField As String
Dim stWhere As String
Dim stLinkCriteria As String
Dim lngView As Long
Const stDateFormat = "\#mm\/dd\/yyyy\#"

stReport = "ErrorReport"
stForm = "ErrorDateRange"
stMacro = "PrintErrorReport"
stDateField = "[tblQALog].[ReviewDate]"
lngView = acViewPreview



If CurrentProject.AllReports(stReport).IsLoaded Then
DoCmd.Close acReport, stReport
End If

stWhere = ""

If IsDate(Me.txtStartDate) Then
stWhere = "([tblQALog].[ReviewDate] >=" & Format(Me.txtStartDate, stDateFormat) & ")"
ElseIf IsDate(Me.txtEndDate) Then
stWhere = "([tblQALog].[ReviewDate] <=" & Format(Me.txtEndDate, stDateFormat) & ")"
End If

If Len(stWhere) > 0 Then
If Len(Nz(Me.cboWorkerName)) > 0 Then
stWhere = stWhere & " AND [WORKER] =" & "'" & Me.cboWorkerName & "'"
End If
Else
If Len(Nz(Me.cboWorkerName)) > 0 Then
stWhere = "[Worker]=" & "'" & Me.cboWorkerName & "'"
End If
End If

If stWhere <> "" Then
DoCmd.OpenReport stReport, lngView, , stWhere
'DoCmd.RunMacro stMacro
'DoCmd.Close acForm, stForm
'DoCmd.Close acReport, stReport
Else
MsgBox "You must enter a worker and date range for this report!", vbOKOnly, "Not enough information!"
End If

End Sub

[nico5038]

You found the reason why I normally use a "dynamic" combo box.
When dates are selected the available workers can differ, by selecting the available workers first this error can't occur.

Nic;o)

[smithj14]

I ams sorry but I do not know anything about using dynamic combobox with a date range form and reports. Is this something you can assist with?

[nico5038]

To start with you can create a new combobox and use a "SELECT DISTINCT ReviewDate from tblQALog Order by 1 DESC".
This can be used for the startdate.

Best probably to start with a combo for the employees. This will allow to select all ReviewDate values that are available for an employee.

This will change the above "SELECT" into a "SELECT DISTINCT ReviewDate from tblQALog WHERE employeeID = " & Me.cmbEmployee & " Order by 1 DESC"

Filling this Startdate combobox will be needed when the employee combobox has been selected, thus we set the rowsource of the combobox in the cmbEmployee's AfterUpdate event and first we test or there has been a selection of an employee like:

IF Len(nz(Me.cmbEmployee)) > 0 then
   Me.cmbStartdate.rowsource = "SELECT" into a "SELECT DISTINCT ReviewDate from tblQALog WHERE employeeID = " & Me.cmbEmployee & " Order by 1 DESC"
else
   msgbox "Please select an employee"
endif
 

Personally I often make the "dependent" comboboxes visible after a selection has been made of the "master". Thus the user won't see the Start/End combo's when no employee has been selected and I don't have to test or the first fill the employee combo :-)

Getting the idea ?

Nic;o)