Returning Values

[Guest]

I am returning the Min and Max of field [Period] by making two passes through
the database as follows. It is very slow. How can I speed this up and do it
with one pass and speed up the process also?

Thank you for your help.

'----------------------------------------------------
Dim vMin, vMax As String
vMin = retMin("HistoryFile", "Co = '" & [Forms]![FLookup]![Co] & "'")
vMax = retMax("HistoryFile", "Co = '" & [Forms]![FLookup]![Co] & "'")
Me.Combo40 = vMin
Me.Combo42 = vMax
'---------------------------------
Public Function retMin(TableName As String, WhereClause As String) As String
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT Min([Period]) As vMinimum " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSql)
retMin = rs!vMinimum
rs.Close
Set rs = Nothing
End Function


Public Function retMax(TableName As String, WhereClause As String) As String
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT Max([Period]) As vMaximum " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSql)
retMax = rs!vMaximum
rs.Close
Set rs = Nothing
End Function

 

[Marshall Barton]

I am returning the Min and Max of field [Period] by making two passes through
the database as follows. It is very slow. How can I speed this up and do it
with one pass and speed up the process also?

Thank you for your help.

'----------------------------------------------------
Dim vMin, vMax As String
vMin = retMin("HistoryFile", "Co = '" & [Forms]![FLookup]![Co] & "'")
vMax = retMax("HistoryFile", "Co = '" & [Forms]![FLookup]![Co] & "'")
Me.Combo40 = vMin
Me.Combo42 = vMax
'---------------------------------
Public Function retMin(TableName As String, WhereClause As String) As String
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT Min([Period]) As vMinimum " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSql)
retMin = rs!vMinimum
rs.Close
Set rs = Nothing
End Function


Public Function retMax(TableName As String, WhereClause As String) As String
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT Max([Period]) As vMaximum " & _
"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSql)
retMax = rs!vMaximum
rs.Close
Set rs = Nothing
End Function

One query should be twice(?) as fast:

Dim vMin, vMax As String
retMin vMin, vMax, "HistoryFile", _
"Co = '" & [Forms]![FLookup]![Co] & "'"

Me.Combo40 = vMin
Me.Combo42 = vMax

Public Sub retMinMax(MinVal As String, MaxVal As String, _
TableName As String, WhereClause As String) As String

Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT Min([Period]) As vMinimum, " & _
"Max([Period]) As vMaximum " & _

"FROM [" & TableName & "] " & _
"WHERE " & WhereClause

Set rs = CurrentDb.OpenRecordset(strSql)

MinVal = rs!vMinimum
MaxVal = rs!vMaximum

rs.Close
Set rs = Nothing

End Sub

 

[Guest]

I cannot get this to work. I get an error that says "ByRef agrument type
mismatch" on this part --- at the retMin.

Dim vMin, vMax As String
retMin vMin, vMax, "HistoryFile", _
"Co = '" & [Forms]![FLookup]![Co] & "'"
Me.Combo40 = vMin
Me.Combo42 = vMax

The Sub being called is retMinMax so does this need to say retMinMax vs
retMin? I also dont follow the structure. Is and = sign and/or () needed
somewhere here?

Thanks

 

[Marshall Barton]

I cannot get this to work. I get an error that says "ByRef agrument type
mismatch" on this part --- at the retMin.

Dim vMin, vMax As String
retMin vMin, vMax, "HistoryFile", _
"Co = '" & [Forms]![FLookup]![Co] & "'"
Me.Combo40 = vMin
Me.Combo42 = vMax

The Sub being called is retMinMax so does this need to say retMinMax vs
retMin? I also dont follow the structure. Is and = sign and/or () needed
somewhere here?

Yes the procedure call should be retMinMax. Sorry, I missed
that change. I just noticed that I also forgot to remove
the As String from the end of the procedure's declaration.

The structure uses a Sub procedure that assignes the two
variables vMin and vMax directly in the procedure using the
default ByRef style argument. If you want to make that
explicit, declare the procedure this way:

Public Sub retMinMax( ByRef MinVal As String, _
ByRef MaxVal As String, _
ByVal TableName As String, _
ByVal WhereClause As String)

and since it's a Sub, not a Function procedure, you can not
use a = sign.

Calling a Sub procedure this way does not use parenthesis:

retMinMax vMin, vMax, "HistoryFile", _
"Co = '" & [Forms]![FLookup]![Co] & "'"

The alternate Sub procedure call syntax would be:

Call retMinMax(vMin, vMax, "HistoryFile", _
"Co = '" & [Forms]![FLookup]![Co] & "'")

which does use parenthesis, but if you leave out Call, then
you also leave out the ( )

I don't see how you would get that error message, but who
knows what's happening when I called the wrong procedure.

 

[Guest]

Works great now. Thank you for your help.