D J
asked on
MS Access How can I add the search dates to my report?
The code below generates a report with the From & To dates inputted by the user.
How can I put these dates in the header of the report?
Example: 6/1/13 to 6/30/13
How can I put these dates in the header of the report?
Example: 6/1/13 to 6/30/13
Private Sub btnReport_Click()
DoCmd.OpenReport "rptWeekly", acViewPreview, , "refdate Between " & "#" & Me.txtStartDate & "#" & " AND " & "#" & Me.txtEndDate & "#"
End Sub
Leave the form open in the background, and in the Open Event of the report:
Me.txtStartDate = Forms!YourFormName.txtStar tDate
Me.txtEndDateDate = Forms!YourFormName.txtEndD ate
Me.txtStartDate = Forms!YourFormName.txtStar
Me.txtEndDateDate = Forms!YourFormName.txtEndD
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have saved the dates to custom database properties, you can use them in an unbound control in the report header, like this:
="Air Tests by Date from " & Format(GetProperty("FromDa te",""),"m /d/yyyy") & " to " & Format(GetProperty("ToDate ",""),"m/d /yyyy")
Here is some code for working with custom database properties (one of the advantages is that you don't have to have a form open, once you have saved the dates to properties):
="Air Tests by Date from " & Format(GetProperty("FromDa
Here is some code for working with custom database properties (one of the advantages is that you don't have to have a form open, once you have saved the dates to properties):
Option Compare Database
Option Explicit
Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strPropertyValue As String
Private lngDataType as Long
Private varPropertyValue As Variant
Public Sub SetProperty(strName As String, lngType As Long, _
varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures
On Error GoTo ErrorHandler
'Attempt to set the specified property
Set dbs = CurrentDb
Set prps = dbs.Properties
prps(strName) = varValue
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err.Number = 3270 Then
'The property was not found; create it
Set prp = dbs.CreateProperty(Name:=strName, _
Type:=lngType, Value:=varValue)
dbs.Properties.Append prp
Resume Next
Else
MsgBox "Error No: " & Err.Number _
& " in SetProperty procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
Public Function GetProperty(strName As String, strDefault As String) _
As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures
On Error GoTo ErrorHandler
'Attempt to get the value of the specified property
Set dbs = CurrentDb
GetProperty = dbs.Properties(strName).Value
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3270 Then
'The property was not found; use default value
GetProperty = strDefault
Resume Next
Else
MsgBox "Error No: " & Err.Number _
& " in GetProperty procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Function
Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)
On Error Resume Next
Set dbs = CurrentDb
Debug.Print "Database properties:"
For Each prp In dbs.Properties
Debug.Print vbTab & prp.Name & ": " & prp.Value
Next prp
End Function
==================================
Usage examples:
Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant
Date
====
If IsDate(Me![txtStartDate].Value) = False Then
strTitle = "Invalid date"
strPrompt = "Please enter a valid start date"
GoTo ErrorHandlerExit
Else
dteStart = CDate(Me![txtStartDate].Value)
End If
strPropertyName = "PropName"
lngDataType = dbDate
Call SetProperty(strPropertyName, lngDataType, dteStart)
GetStartDate = CDate(GetProperty("PropName", ""))
Text
====
strPropertyName = "PropName"
strPropertyValue = CStr(cbo.Value)
lngDataType = dbText
Call SetProperty(strPropertyName, lngDataType, _
strPropertyValue )
strDocsPath = GetProperty("PropName", "")
Long
====
strPropertyName = "PropName"
lngDataType = dbLong
Call SetProperty(strPropertyName, lngDataType, lngID)
lngID = CLng(GetProperty("PropName", ""))
Integer
=======
strPropertyName = "PropName"
lngDataType = dbInteger
Call SetProperty(strPropertyName, lngDataType, intMonth)
intID = CInt(GetProperty("PropName", ""))
Boolean
=======
strPropertyName = "PropName"
lngDataType = dbBoolean
Call SetProperty(strPropertyName, lngDataType, "True")
blnValue = CBln(GetProperty("PropName", ""))
Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009
On Error GoTo ErrorHandler
If IsDate(Me![txtDate].Value) = True Then
dteSingle = CDate(Me![txtDate].Value)
strPropertyName = "SingleDate"
Call SetProperty(strName:=strPropertyName, _
lngType:=dbDate, varValue:=dteSingle)
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit
End Sub
ASKER
Thanks!
Then:
Private Sub btnReport_Click()
DoCmd.OpenReport "rptWeekly", acViewPreview, , "refdate Between " & "#" & Me.txtStartDate & "#" & " AND " & "#" & Me.txtEndDate & "#"
With Reports("rptWeekly")
.txtStartDate = Me.txtStartDate
.txtEndDate = Me.txtEndDate
End With
End Sub
/gustav