Using Min and MAX in Report Control Source (1 Viewer)

gcarpenter

Registered User.
Local time
Today, 05:34
Joined
Oct 21, 2013
Messages
68
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

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Why don't you want to use the Min() function? Just curious...
 

Minty

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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

Registered User.
Local time
Today, 05:34
Joined
Oct 21, 2013
Messages
68
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
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

AWF VIP
Local time
Today, 09:34
Joined
Jul 26, 2013
Messages
10,353
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

Code:
DoCmd.OpenReport "rpt_YourReportName"

You can add a filter to that code ;

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

Users who are viewing this thread

Top Bottom