Using Min and MAX in Report Control Source

[gcarpenter]

I have a field in the query called Shipdate. I want to use the Min date of this field in a textbox on a report. I don't want to restrict the query since it feeds other reports where I don't want to use the Min function. I keep getting the results in the report as #Error.

 

[theDBguy]

Why don't you want to use the Min() function? Just curious...

 

[Minty]

What is the code you are using to open the report?

You should be able to apply a filter to the report in the open command.

 

[gcarpenter]

Code to open report;
Option Compare Database
Option Explicit
Dim RetVal As Variant
Dim CloseDialog As Boolean
Dim SPTotal As Currency
Dim RptTotal As Currency

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
' txtSPTotal = SPTotal
End Sub


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'SPTotal = SPTotal + Me!AMOUNTCLOSED
'RptTotal = RptTotal + Me!AMOUNTCLOSED
End Sub


Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
SPTotal = 0
End Sub


Private Sub Report_Close()

RetVal = SysCmd(5)
Call LogDocClose(Me)
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No claims match the report criteria."
Cancel = True
CloseDialog = False
End Sub




Private Sub Report_Open(Cancel As Integer)
Call LogDocOpen(Me)
RetVal = SysCmd(4, "This is the report based on the criteria chosen...")


End Sub


Private Sub Report_Resize()
DoCmd.Maximize
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'txtRptTotal = RptTotal
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
RptTotal = 0
End Sub

 

[gcarpenter]

Why don't you want to use the Min() function? Just curious...

The query is the source for other reports that I don't want to use the min shipdate.

 

[theDBguy]

The query is the source for other reports that I don't want to use the min shipdate.

Oh, I thought you were saying you didn't want to use the Min() function in the report. If you are saying you don't want to use the Min() function in the query, then you should be able to use it in the report.

 

[Minty]

Code to open report;
Option Compare Database
Option Explicit
Dim RetVal As Variant
Dim CloseDialog As Boolean
Dim SPTotal As Currency
Dim RptTotal As Currency

...

This is the reports' code, not how you open it.
Somewhere you should have

DoCmd.OpenReport "rpt_YourReportName"

You can add a filter to that code ;

DoCmd.OpenReport "rpt_YourReportName", acViewPreview, , "YourDateField <= #" & Format(Me.txtMyDateCriteria,"yyyy-mm-dd") & "#"