Adding given from symbol flap in request

Sometimes can be necessary to pack in request symbol fields. For instance: there is database about constructions. One of the parameter of the description of the constructions is a description material, from which buildings are built. For one building can be used several material: brick, газосиликатный brick, concrete blocks, concrete overlappings, tree, металлоконструкции etc. The Amount material can be free.

We Create in the base two tables - a table of the buildings tblOsnFond and table material tblMaterial. Afterwards we create the third table tblOFmaterial, where shall bring the relationship between constructions and мотериалами, from which it is built. All rather standard. Easy get the list a material, from which is made construction, and easy get the list of the constructions, in which is used determined material. But has here is come the time to create the report (or the form), in which must be a columns (the field) "Construction" and "Material", In field "Material" must be enumerated all material, which were used at construction. Use in request function Sum() for adding (the mergings) of symbol importances does not introduce possible therefore that function Sum() applicable only for scalar(!!!) of importances. А for symbol she does not work. And really:

3+2=2+3, но "3"+"2"<>"2"+"3" "32"<>"23"

To pack importances of the symbol field it is necessary most write the function of the adding symbol (string) of importances and substitute her(it) in request. This function must be disposed in separate module (rather then in module of the form or report) and have a characteristic Public Such function already possible to substitute in request. Here is example to functions:

Public Function fnSumString(LngOsnFond As Long) As String

Dim sResult As String

On Error GoTo fnSumString_Error
Dim RstX As ADODB.Recordset
Dim strSQL As String

      Set RstX = New ADODB.Recordset
      strSQL = "SELECT * FROM qryOFmaterial WHERE IdOsnFond=" & LngOsnFond

      RstX.Open strSQL, CurrentProject.Connection, adOpenKeyset

      ' initial apropriation of importance
      sResult = ""
      ' проверка на наличие записей в запросе
      If RstX.RecordCount > 0 Then
            Do
                  sResult = sResult & (IIf(Len(sResult) > 0, ", ", "") & Nz(RstX.Fields("Material"), ""))
                  RstX.MoveNext
            Loop Until RstX.EOF 
      End If
      RstX.Close
      Set RstX = Nothing
      fnSumString = sResult

On Error GoTo 0
Exit_fnSumString:
Exit Function

fnSumString_Error:
      MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") in procedure fnSumString в Module       Module1"
      Resume Exit_fnSumString
End Function

This function conducts the summation of importances of the field "Material" for each building.She is not universal (i.e on all events of the lifes), but her(its) possible дорабатывать for their own necessities. But here is example of the request with use of this functions:

SELECT qryOFmaterial.IdOsnFond, qryOFmaterial.OsnFond, fnSumString(qryOFmaterial.IdOsnFond) AS MaterialSum
FROM qryOFmaterial
GROUP BY qryOFmaterial.IdOsnFond, qryOFmaterial.OsnFond;

Here is strictly and all. But since presently many begin to move to MS SQL Server, that follows to tell as this problem dares and there. It There is too used user function, but not written in program, but хранимая on server. Solves she same most tasks. Here is her(its) text:

ALTER FUNCTION [dbo].[fn_OFmaterialS]
(@id INTEGER)
RETURNS NVARCHAR(100)
AS
BEGIN
      DECLARE @Result NVARCHAR(4000)
      SET @Result = ''
      SELECT @Result = @Result + case when [Material] Is null then '' else case when @Result<>'' then ', '+[Material] else [Material] end end
      FROM vw_OsnFondMaterial WHERE [IdOsnFond] = @Id
      RETURN  @Result
END

Autor: Joss It Is Added: 01.08.2008