Need help with concatenating fields

[Television]

Greetings!

I have a following problem: I need to concatenate fields in one of my tables depending of several conditions based on different fields in same table. Here is sample row from the table.

NonDiagnosticsFindsMainTable
ID: 7
LocusID: 2
LocusNr: 2.2
MaterialID: 1
MaterialName: Pottery
SubtypeID: 1
MaterialSubtype: Amphora
CharacteristicID: 5
MaterialCharacteristic: Body
MaterialAmount: 2
MaterialWeight:
MaterialNotes:

Fields to concatenate are MaterialCharacteristic and MaterialAmount. First criteria is LocusNr, then only Pottery in MaterialName and final criteria is MaterialSubtype. One Locus can have several (or none) pottery subtypes and each subtype can have many (but at least one) characteristics. What I need is something like this:

Locus: 2.2; Materialottery; Subtype:Amphora; Characteristic(s):Rim, Base, Handle; Amount: 2, 3, 4

In words; one row would contain all the information of particular pottery subtype
from particular Locus.

I have banged my head on this several days but with little success. I have tried to use Allen Browne's ConcatRelated function, but I don't seem to send correct parameters to function. Latest error message is "Error 3061. Too few parameters. Expected 1" from this code:

ConcatRelated("[MaterialCharacteristic]", "[FindsFromNonDiagMainTbl]", "[SubtypeID] = " & [tempSubID])

I have tried several approaches to this function but every time I add optional parameters some kind of error occurs.
Here is code from forms On_Current code if it helps to pinpoint my errors:

Option Compare Database
Option Explicit


Private Sub Form_Current()
    Dim tempAmount As String
    Dim tempChars As String
    Dim tempSubID As Variant
    Dim tempLocusNr As String
    Dim tempMaterialID As Variant
    Dim tempMaterial As Variant
    tempMaterial = Me.MaterialName
    If tempMaterial = "Pottery" Then
        tempLocusNr = Me.LocusNr
        tempMaterialID = Me.MaterialID
        tempSubID = Me.SubtypeID
        tempChars = ConcatRelated("[MaterialCharacteristic]", "[NonDiagnosticsFindsMainTable]", "[LocusNr] = """ & [tempLocusNr] & """", "[MaterialID] = " & [tempMaterialID], "[SubtypeID] = " & [tempSubID])
        tempAmount = ConcatRelated("[MaterialAmount]", "[NonDiagnosticsFindsMainTable]", "[LocusNr] = """ & [tempLocusNr] & """", "[MaterialID] = " & [tempMaterialID], "[SubtypeID] = " & [tempSubID])

    End If
  
End Sub

"FindsFromNonDiagMainTbl" is a name of query that I use to get records from one Locus based on previous forms in data hierarchy.
There will be more code in this form, but first I need to solve this concatenate problem.

Basically I have two problems: To combine fields and present combined strings in one record of form (either in datasheet view or in continuous form).

I would be really grateful if someone points me to right direction. I am more than happy to provide more info if asked.

Thank you in advance!

Television

Edit: Added Code Tags and updated code. No more error messages and I am starting to go where I want to go. Bit more polishing needed, but that can wait until I have come back from a vacation. Thanks again Eugin for pointing me to right direction! Still need to find solution to my other problem though.

 

[pr2-eugin]

Television, Allen Browne's code is quiet robust, with your description it is quiet unclear where the error falls, It would be best if you could..

1. Edit the code by using CODE tags, so code is properly indented..
2. Highlight in the code, the line where the Error occurs..

General help for Too few parameters, if your code involved a SAVED query that needs parameter to run on its own and you base that Query to be executed in RunTime.. Then you will be facing this error..

 

[Television]

Thanks Eugin. My query indeed runs on parameter that it gets from a form. I didn't know that it would be a problem. I will adjust my code, modify Browne's code to accept more parameters and post results here. I will also edit my previous post to be more standard (after a meeting of course *sigh*).

Television

Television, Allen Browne's code is quiet robust, with your description it is quiet unclear where the error falls, It would be best if you could..

1. Edit the code by using CODE tags, so code is properly indented..
2. Highlight in the code, the line where the Error occurs..

General help for Too few parameters, if your code involved a SAVED query that needs parameter to run on its own and you base that Query to be executed in RunTime.. Then you will be facing this error..