Returning Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven - 27 Jan 2005 05:05 GMT

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

Reply to this Message

Marshall Barton - 27 Jan 2005 05:48 GMT

>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
[quoted text clipped - 36 lines]
> 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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Steven - 27 Jan 2005 16:29 GMT

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

Reply to this Message

Marshall Barton - 27 Jan 2005 18:56 GMT

>I cannot get this to work. I get an error that says "ByRef agrument type
>mismatch" on this part --- at the retMin.
[quoted text clipped - 8 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Steven - 27 Jan 2005 21:33 GMT

Works great now. Thank you for your help.

Reply to this Message