By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,436 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Date Range on a report??

smithj14
P: 18
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
Oct 6 '08 #1
Share this Question
Share on Google+
12 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 6 '08 #2

smithj14
P: 18
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.
Oct 7 '08 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 7 '08 #4

P: 69
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
Oct 7 '08 #5

smithj14
P: 18
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?
Oct 7 '08 #6

nico5038
Expert 2.5K+
P: 3,072
You'll need to code all combinations of dates and worker filled and/or empty.
Would look something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdErrorReport_Click()
  2.  
  3. Dim stWhere As String
  4.  
  5. stWhere = ""
  6.  
  7. If IsDate(Me.txtStartDate) and IsDate(Me.txtEndDate) Then
  8.    stWhere = "([ReviewDate] between #" & Format(Me.txtStartDate, "mm-dd-yyyy") & "and & Format(Me.txtEndDate, "mm-dd-yyyy") & ")"
  9. elseif IsDate(Me.txtStartDate) then
  10.    stWhere = "([ReviewDate] >=" & Format(Me.txtStartDate, "mm-dd-yyyy") & ")"
  11. elseif IsDate(Me.txtEndDate) then
  12.    stWhere = "([ReviewDate] <=" & Format(Me.txtEndDate, "mm-dd-yyyy") & ")"
  13. End If
  14.  
  15. ' stWhere filled?
  16. IF len(stWhere) > 0 then
  17.    if len(nz(Me.cmbWorker)) > 0 then
  18.       ' cmbWorker filled and stWhere
  19.       stWhere = stWhere & " AND ([Worker] =" & Me.cmbWorker & ")"
  20.    endif
  21. else
  22.    if len(nz(Me.cmbWorker)) > 0 then
  23.       ' cmbWorker filled and stWhere empty
  24.       stWhere = "([Worker] =" & Me.cmbWorker & ")"
  25.    endif
  26. endif
  27.  
  28. If stWhere <> "" Then
  29.    DoCmd.OpenReport "ErrorReport", acViewPreview, , stWhere
  30. else
  31.    DoCmd.OpenReport "ErrorReport", acViewPreview
  32. endif
  33.  
  34. End Sub
  35.  
Just keep the form opened, thus an erroneous selection can be corrected easily by the user.

Nic;o)
Oct 7 '08 #7

smithj14
P: 18
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.
Oct 8 '08 #8

nico5038
Expert 2.5K+
P: 3,072
There's an additional "#" before the first date, remove it from the stWhere filling.

Nic;o)
Oct 8 '08 #9

smithj14
P: 18
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
Oct 9 '08 #10

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 9 '08 #11

smithj14
P: 18
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?
Oct 9 '08 #12

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. IF Len(nz(Me.cmbEmployee)) > 0 then
  2.    Me.cmbStartdate.rowsource = "SELECT" into a "SELECT DISTINCT ReviewDate from tblQALog WHERE employeeID = " & Me.cmbEmployee & " Order by 1 DESC"
  3. else
  4.    msgbox "Please select an employee"
  5. endif
  6.  
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)
Oct 9 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.