ConCatenate Function using multiple criteria in Where Clause Syntax

[Joe Y]

I'm using an Allen Brown concatenate related records code from Allen Browne (http://allenbrowne.com/func-concat.html).

The code works fine when I have only one criterion. The code is like this:

=ConcatRelated("[Allergen]","[Q_Recipe_Allergen]","[recipeID]= """ & [Txt_RecipeID] & """","[ConvWTA]" & "DESC")

Where Allergen and recipeID are text fields in query Q_Recipe_Allerg en.

Txt_RecipeID is the text box name. ConvWTA is a number field that data sorting is based on.

When I tried to add the second criterion RecipeVersion, I cannot get the syntax right. The closest (not working) one I can get is as below. I got error message “Error 3061: Too few parameters. Expect1.”

=ConcatRelated("[Allergen]","[Q_Recipe_Allergen]","[recipeID]= """ & [Txt_RecipeID] & """" & "And [RecipeVersion]=""" & [txtRecipeVer] & """","[ConvWTA]" & "DESC")

I think I got confused with the quotation mark. What should the correct syntax be?

Thanks,
Joe

[Mihail]

I am pretty sure that must be a better solution for you than using the ConcatRelated() function.
If you can be more specific when inform us what you are trying to do I think we can find a better approach.

Any way... try this:
Create a query (in query design view) that do the job (using multiple criteria).
Switch to SQL view.
Copy the Where clause then paste it in your function.

[nico5038]

This message indicates in general a wrong fieldname, so check the added fields to exist in the table [Q_Recipe_Allerg en]

[Joe Y]

Nico5038:

Thanks for the advice. In process of checking file names, I realized that I forgot to include field name [RecipeVersion] in the Query [Q_Recipe_Allerg en]. That's why Access could not find this parameter.

Sorry for the silly question.

[Joe Y]

Mihail,

What I was trying to do using ConCatenate function is explained below.

Ingredient statement and allergen warning on Food Label are accumulating result of components used in the recipe sorting by the quantity order of predominant.

In one of my forms, I have a datasheet sub-form serves as my formulation work book. It lists all ingredients and the percentage of each ingredient. Each ingredient’s legitimate ingredient statement and allergen(s) are also part of the datasheet sub-form.

When a user finalized a recipe, in my main form, there are two text boxes that should automatically list ingredient statement and allergens using the logic explained above.

Due to the lack of VBA experiences, I wasn’t able to get this done for a very long time. I tried Access’ aggregate functions, such as Dlookup, Dsum, but it won’t work in my case. Recently, I found Allen Browne’s ConCatenate function and it works just fine.

If you have better and simple way to achieve this, Please advise. It certainly will help a lot in my database.

Thanks,
Joe