Allen Browne ConcateRelated "Enter Parameter Value"

[ram_rtg]

Hi all,

I am having trouble with the concatenate function code on the Allen Browne website located here
http://allenbrowne.com/func-concat.html

I am using the textbox controlsource method on a report:
=ConcatRelated("ID", "qryFutureReleaseDate", "FMonth = " & [FMonth])

The report is based on a query and when I run it I get an input box "Enter Parameter Value" ConcatRelated.

It seems like the Function is not registering with the Database.

Anyone know what might be causing this?

Thanks in advance for your help.

 

[arnelgp]

is your query qryFutureReleaseDate a Parameter query? this function only works on non-parameter query and non-QBF.

 

[ram_rtg]

is your query qryFutureReleaseDate a Parameter query? this function only works on non-parameter query and non-QBF.

I think it's just a simple query:

SELECT Year([Release date required]) AS FYear, Month([Release date required]) AS FMonth, Count(tblDataEntry.ID) AS CountOfID, Sum(tblDataEntry.[Estimated new files]) AS [SumOfEstimated new files], Sum(tblDataEntry.[Estimated existing files]) AS [SumOfEstimated existing files]
FROM tblDataEntry
GROUP BY Year([Release date required]), Month([Release date required])
HAVING (((Year([Release date required]))=[Forms]![frmSelectFutureYear]![Year])) OR ((([Forms]![frmSelectFutureYear]![Year]) Is Null));

 

[arnelgp]

as ive said this function will not work on QBF (query by form), so you have a criteria there referring to a form: [Forms]![frmSelectFutureYear]![Year]. you have to convert this to literal values.

 

[ram_rtg]

as ive said this function will not work on QBF (query by form), so you have a criteria there referring to a form: [Forms]![frmSelectFutureYear]![Year]. you have to convert this to literal values.

Sorry I didn't realise what QBF was, how do I convert to literal values?

 

[ram_rtg]

Ok, so I now know what literal values are and it looks like the Allen Browne solution is no longer viable.

Is there a way to concatenate the values from this query into a single textbox in my report without applying literal values?

All I want my report to do is group by year then month and then list and count the ID number for that month. The year can be input through the form by the user or if it is left blank then the report is generated for all the years in the database.

Does anyone know if this is possible?

I'm happy to consider other coding methodologies

 

[arnelgp]

you can still used the concatrelated function though.
edit your report in design view. add a textbox control on the Page Header of your report (call this txtRecordSource). set the Visible property of this textbox to Visible=False. now, on your other textbox (the one you want to use concatRelated), set its control source to:
=ConcatRelated("ID", [txtRecordSource], "FMonth = " & [FMonth])

now go to your report's Open event, and paste this code:

Private Sub Report_Open(Cancel As Integer)

    
    Dim intYear As Integer
    
    If IsFormLoaded("frmSelectFutureYear") Then
        intYear = Val(0 & Forms!Year)
        strRecordSource = "SELECT Year([Release date required]) AS FYear, " & _
            "Month([Release date required]) AS FMonth, " & _
            "Count(tblDataEntry.ID) AS CountOfID, " & _
            "Sum(tblDataEntry.[Estimated new files]) AS [SumOfEstimated new files], " & _
            "Sum(tblDataEntry.[Estimated existing files]) AS [SumOfEstimated existing files] " & _
            "FROM tblDataEntry "
            "GROUP BY Year([Release date required]), Month([Release date required]) " & _
            "HAVING (((Year([Release date required])) = " intYear & " " & _
            "OR intYear = 0"
        Me.RecordSource = strRecordSource

    Else
        Cancel = True
    End If
End Sub

Private Sub Report_Load()
        Me.txtRecordSource =  "(" & strRecordSource & ")"
End Sub

on a Module paste this:

Public Function IsFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    IsFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then IsFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

 

[ram_rtg]

Hey thanks for this code, I have tried it and there is a bit where the code is in red font color:

"GROUP BY Year([Release date required]), Month([Release date required]) " & _
"HAVING (((Year([Release date required])) = " intYear & " " & _
"OR intYear = 0"

When I run this code I get a compile eror/ syntax error message so I'm thinking it's missing an extra " somewhere?

When I add an "& _" after "FROM tblDataEntry " like this:

 "FROM tblDataEntry " & _

The whole lot turns red and it gets stuck at intYear.

 

[arnelgp]

please try

 

[ram_rtg]

please try

This is EXACTLY what I wanted thanks heaps.

All I need to do now is work out the Expression to use in the Report Grouping that was originally derived from the query and the count of those IDs you helped me with (they all defaulted to "1" for some reason).
I'll take a look at those tomorrow

 

[ram_rtg]

Oh! I just realised the ConcatRelated function is concatenating all the month regardless of the year they are in.
I changed the textbox source to
=ConcatRelated("ID",[txtRecordSource],"FYear = " & [FYear] And "FMonth = " & [FMonth])
but this returns all TaskIDs for every matching Year and Month whereas I need it to return the TaskIDs by month for each year separately.

 

[Cronk]

I'm surprised you don't get a syntax error.

Try

 ConcatRelated("ID",[txtRecordSource],"FYear = " & [FYear] & " And FMonth = " & [FMonth])

 

[ram_rtg]

I'm surprised you don't get a syntax error.

Try

 ConcatRelated("ID",[txtRecordSource],"FYear = " & [FYear] & " And FMonth = " & [FMonth])

Works a treat thanks!