Using VB to implement some small function sets

1. Similar to max () in Excel ()

 

Public Function getmax (paramarray item () as variant) as Variant

Dim omax as Variant
Dim I as integer

Omax = item (I)

For I = 1 to ubound (item)
If omax <item (I) then
Omax = item (I)
End if
Next I

Getmax = omax

End Function

2. dconcat domain string merging function

Dconcat Functions

You can use the dconcat function to connect strings of values in a specified set of records (domains.
You can use the dconcat function in Visual Basic, Macro, query expression, or calculation control.
(ADO/Dao is not supported ).

For example, you can use the dconcat function in the query.
Select customerid, dconcat ('orderid', 'Orders ', 'mermerid =' & customerid
From MERs

Dconcat (expr, domain, [criteria], [delimiter])
The dconcat function has the following parameters.

Parameter description
Expr expression used to identify the field for calculating the number of records. It can be an identifier table or a query.
The string expression of a field. It can also be an expression used to calculate the data in the field.
Expr can contain the table field name, controls on the form, constants, or functions.
If expr contains a function, it may be a built-in or user-defined function,
It is not another domain aggregate function or SQL aggregate function.
A domain string expression that identifies a group of records in a domain. It can be a table name or does not need
The name of the query that you want to query.
Criteria can be an optional string expression used to limit the range of the target data for which the dconcat function is executed
Perimeter. For example, criteria is usually equivalent to the WHERE clause in an SQL expression, but it
No word where. If criteria is omitted, the dconcat Function
Calculate expr for the entire domain. Any field contained in criteria must be domain
Otherwise, the dconcat function returns an error message.
Delimiter is an optional string expression used to separate the string values of each record. The default value is comma ",".

Function implementation

'*************************************** ****************************************
'Dconcat (expr, domain, [criteria], [delimiter])
'
'Function: To concate the columns string like group_concat () in access.
'Parameter:
'Expr: String, expression which can recognized by SQL.
'Domain: string, the row source, can be another query.
'Criteria: The ceri1_which will be treated as the WHERE clause.
'Delimiter: delimiter between the column value, default is ",".
'
'Return: String, a string of concated columns,
'If err, return the err code + DESC.
'
'History:
'2009-feb-28 acmain New Creation
'
'*************************************** **************************************** **

Public Function dconcat (sexpr as string, sdomain as string, optional scriteria as string, optional sdelimiter as string = ",")
On Error goto errhandler

Dim RS as new ADODB. recordset

Dim ssql as string
Dim sresult as string
Sresult = ""

Ssql = "select" & sexpr & "from (" & sdomain &")"
If scriteria <> "" then
Ssql = ssql & "where" & scriteria
End if

Rs. Open ssql, currentproject. Connection, adopenforwardonly, adlockreadonly

Do while not Rs. EOF
If sresult <> "then
Sresult = sresult & sdelimiter
End if
Sresult = sresult & Rs. Fields (0). Value
Rs. movenext
Loop

Rs. Close
Set rs = nothing

Dconcat = sresult

Exit Function

Errhandler:
If Rs. State <> adstateclosed then
Rs. Close
End if
Set rs = nothing

Dconcat = err. Number & ":" & err. Description


End Function