By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

ConCatenate Function using multiple criteria in Where Clause Syntax

P: 68
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:

Expand|Select|Wrap|Line Numbers
  1. =ConcatRelated("[Allergen]","[Q_Recipe_Allergen]","[recipeID]= """ & [Txt_RecipeID] & """","[ConvWTA]" & "DESC")
Where Allergen and recipeID are text fields in query Q_Recipe_Allergen.

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.”

Expand|Select|Wrap|Line Numbers
  1. =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
May 3 '12 #1

✓ answered by nico5038

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

Share this Question
Share on Google+
4 Replies


100+
P: 759
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.
May 3 '12 #2

nico5038
Expert 2.5K+
P: 3,072
This message indicates in general a wrong fieldname, so check the added fields to exist in the table [Q_Recipe_Allergen]
May 3 '12 #3

P: 68
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_Allergen]. That's why Access could not find this parameter.

Sorry for the silly question.
May 4 '12 #4

P: 68
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
May 4 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.