need help with report 1

[javaguy1]

i created a query to base my report on and set a criteria for the date field....Between [Enter Start Date] And [Enter End Date]
how can i display the values the user enters in the report?

 

[wonderwhy]

Add a text box to your report where you want the dates to display and type something like this in the Control Source field (in Properties for the text box):
="Show me "&[DateField]&" and "&[DateField2]"."

Replace [DateField] and [DateField2] with real field names from your query and change the text above to whatever you need.

Hope it works,

Julia

 

[javaguy1]

well, the query just has one date field. the criteria causes the popup dialogue boxes??????

 

[wonderwhy]

Then type something like this in the Control Source field:

=" You have chosen all records between " & Min([DateField]) & " and " & Max("DateField&quot

Good Luck,

Julia

 

[javaguy1]

that looks reasonable, ill try that thanks

 

[GoDawgs]

Use the same parameter name that you use in the query...so have a text box at the top of the report with this recordsource:

="Data for " & [Enter Start Date] & " to " & [Enter End Date]

Make sure the the parameters are spelled and spaced EXACTLY the same in both the query and the report. Hope that helps.

Kevin

 

[AccessHawaii]

I always try to avoid referencing form field in queries because then you can only use that query with that 1 form, I prefer to set the date range criteria in VBA and reference 2 unbound text boxes on the form. You might want to try this instead.

'Build SQL string to filter report
Dim strDateDiff As String
Dim strDateRange As String

'Ensure both date fields are completed
If IsNull(Me.txtBegin.Value) Or IsNull(Me.txtEnd.Value) Then
MsgBox "You must complete both date fields"
Exit Sub
End If

'Ensure that beginning date is prior or equal to ending date
strDateDiff = DateDiff("d", Me.txtBegin.Value, Me.txtEnd.Value)
If strDateDiff < 0 Then
MsgBox &quot;Beginning Date must be equal to or prior to ending date&quot;
Exit Sub
End If

strDateRange = &quot;([DateRec] between #&quot; & Me.txtBegin.Value & &quot;# and #&quot; & Me.txtEnd.Value & &quot;#)&quot;

DoCmd.OpenReport &quot;rptDuplicateMemberProvider&quot;, acViewPreview, , strDateRange

As far as displaying the user's date range selection on the report. Create a label on your report and reference it in VBA to set its caption to the values the user entered on the form.

Dim strBegin As String
Dim strEnd As String
Dim strTitle As String

DoCmd.Maximize

strBegin = Forms!frmReports!frmSub.Form.txtBegin
strEnd = Forms!frmReports!frmSub.Form.txtEnd
strTitle = &quot;for the Period of &quot; & strBegin & &quot; to &quot; & strEnd
Me.lblDate.Caption = strTitle

Hope this helps.