USE [Mercury]
GO

/****** Object:  UserDefinedFunction [dbo].[getShowContactsForDepartment_AsString]    Script Date: 04/05/2013 14:03:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] 
(
	@ShowNumber varchar(8), @ProductionDepartmentId integer
)
RETURNS varchar(500)
AS
BEGIN

	DECLARE @names VARCHAR(500)

	SET @names = ''

	SELECT 
		@names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
	FROM 
		vw_ShowContacts 
	WHERE 
		ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
	ORDER BY
		Id
	
	--Contingent if @names is a zero-length string
	IF Len(@names) > 0 
	BEGIN
		SET @names = Right(@names,LEN(@names)-1)
	End
	
	RETURN @names

END


GO

Sample usage... 
This code builds the parameters passed to the UpdatePassthroughQuery sub by looking up the RecordSource from a table (eg: view_UsageByDate) and then builds the parameters by looping through the controls on a form to determine if a value has been entered. It is a rather nifty approach to supplying criteria driven reports where the criteria is not known and needs to be flexible </tooHorn>.

        If Me.pg1_lstReports.Column(3) = True Then
'Lookup the SQL Server view/recordsource to us            
strRecordSourceName = Nz(DLookup("txtRecordSourceName", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
            strRecordSourceStatement = Nz(DLookup("txtRecordSourceStatement", "qry_sp_getReportDefinitions", "[lngId] = " & [Forms]![frmReports]![pg1_lstReports]), "")
            'How to test if either are invalid
            'Loop through the criteria for the report, check if it exists in strRecordSourceStatement and replace
            'Not certain how I feel about this over all
            CriteriaArray = Split(Me.pg1_lstReports.Column(2), ";")
            For i = 0 To UBound(CriteriaArray)
                ControlSetting = Split(CriteriaArray(i), "=")
                If InStr(1, strRecordSourceStatement, ControlSetting(0)) > 0 Then
                    strRecordSourceStatement = Replace(strRecordSourceStatement, ControlSetting(0), Me.Controls(ControlSetting(0)))
                End If
            Next i
            Call updatePassThroughQuery(strRecordSourceName, strRecordSourceStatement)
        End If

Public Sub updatePassThroughQuery(strQryDefName As String, strStatement As String)

    'Updates a pass through query to allow parameters to be passed in
    'For ease of development the pass through is created if it doesn't exist
    'http://support.microsoft.com/kb/131534

    Dim MyDb As Database
    Dim MyQ As QueryDef
    Dim QryDefExists As Boolean
    Dim i As Integer
    
    Set MyDb = CurrentDb()

    QryDefExists = False
    
    For i = 0 To MyDb.QueryDefs.Count - 1
        Debug.Print MyDb.QueryDefs(i).Name
        If MyDb.QueryDefs(i).Name = strQryDefName Then QryDefExists = True
    Next i

    If QryDefExists Then
        Set MyQ = MyDb.QueryDefs(strQryDefName)
        MyQ.SQL = strStatement
        MyQ.Close
        MyDb.Close
        Set MyQ = Nothing
    Else
        Set MyQ = MyDb.CreateQueryDef(strQryDefName)
        MyQ.Connect = "ODBC;DSN=TrailerManagementSystem;Description=Connection Trailer Management System SQL Server;Trusted_Connection=Yes"
        MyQ.ReturnsRecords = True
        MyQ.SQL = strStatement
        MyQ.Close
        MyDb.Close
        Set MyQ = Nothing
    End If

    Set MyQ = Nothing
    Set MyDb = Nothing
    
End Sub