Instr too complex! (1 Viewer)

alexfwalker81

Member
Local time
Today, 03:40
Joined
Feb 26, 2016
Messages
93
I'm writing a database to check an ingredients list for allergens. I was rather pleased with myself to have used the InStr function to search for allergens.

For example, this code searches the [ingredients] for the word peanut, and compares it to whether the respondent (a manufacturer) has correctly flagged the presence of peanuts in the ingredients (present or not) in the [CONTAINS Peanuts] field.

PEANUT CHECK: IIf((IIf((InStr([ingredients],"peanut"))<>0,1,0)+IIf([CONTAINS Peanuts]<>"Yes",1,0))>1,"Peanuts in ingredients, but not in CONTAIN statement",IIf((IIf((InStr([ingredients],"peanut"))<>0,1,0)+IIf([CONTAINS Peanuts]<>"Yes",1,0))<1,"Peanuts in CONTAIN statement, but not in ingredients"))

This is possibly not the most elegant way of doing things, but it works, so I'm happy. Things get a bit more complicated when you're looking at an allergen which covers multiple foods. For example, tree nuts are considered an allergen, but you can't search for 'tree nuts', instead, you're looking for multiple things, such as 'almonds', 'hazelnuts', 'macadamia' etc.

No problem, I thought, I'll just build out the code above to have multiple InStr checks, like this;

TREE NUTS CHECK: IIf(IIf(IIf(InStr([ingredients],"almond")<>0,1,0)+IIf(InStr([ingredients],"hazelnut")<>0,1,0)+IIf(InStr([ingredients],"walnut")<>0,1,0)+IIf(InStr([ingredients],"pine nut")<>0,1,0)<>0,1,0)+IIf([CONTAINS Tree Nuts]<>"Yes",1,0)>1,"Tree Nuts in ingredients, but not in CONTAIN statement",IIf(IIf(IIf(InStr([ingredients],"almond")<>0,1,0)+IIf(InStr([ingredients],"hazelnut")<>0,1,0)+IIf(InStr([ingredients],"pine nut")<>0,1,0)<>0,1,0)+IIf([CONTAINS Tree Nuts]<>"Yes",1,0)<1,"Tree Nuts in CONTAIN statement, but not in ingredients"))

This actually works, so I added in a whole load more nut varieties and thought I'd saved the query. However, I'm assuming that I've hit a character or complexity limit, as it just didn't save what I'd done. Having slept on it, it seems to me that I'm better off putting ALL the varieties of tree nuts into a separate table, querying that, and then referencing my tree nuts query to search for matches in the [ingredients] field. The problem is that I don't know the syntax for that!

Any ideas how I could achieve this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:40
Joined
Oct 29, 2018
Messages
21,358
Hi. If you had a table listing allergens and another table listing the ingredients, then it should be a simple INNER JOIN query to find out if any of the ingredients are allergens. No?
 

alexfwalker81

Member
Local time
Today, 03:40
Joined
Feb 26, 2016
Messages
93
Hi. If you had a table listing allergens and another table listing the ingredients, then it should be a simple INNER JOIN query to find out if any of the ingredients are allergens. No?

Ordinarily yes, that's what I'd do. But the problem is that the [ingredients] field is a great long string of text, so I don't think it would match like that. For example, I'm trying to 'hazelnut' in a much longer string of text, rather than a discreet field which would match nicely.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:40
Joined
Oct 29, 2018
Messages
21,358
Ordinarily yes, that's what I'd do. But the problem is that the [ingredients] field is a great long string of text, so I don't think it would match like that. For example, I'm trying to 'hazelnut' in a much longer string of text, rather than a discreet field which would match nicely.
Not necessarily. Can you at least have a distinct list of allergens in a table? If so, you can use a non-equi join with your ingredients table.
 

alexfwalker81

Member
Local time
Today, 03:40
Joined
Feb 26, 2016
Messages
93
Not necessarily. Can you at least have a distinct list of allergens in a table? If so, you can use a non-equi join with your ingredients table.
Yes, the allergens are a distinct list in a table. How would I do a non-equi join?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:40
Joined
Oct 29, 2018
Messages
21,358
Yes, the allergens are a distinct list in a table. How would I do a non-equi join?
Here's an example:
SQL:
SELECT * FROM Allergens INNER JOIN Ingredients ON Ingredients.FieldName Like "*" & Allergens.FieldName & "*"
It's the same as this one:
SQL:
SELECT * FROM Allergens, Ingredients WHERE Ingredients.FieldName Like "*" & Allergens.FieldName & "*"
Hope that helps...
 

alexfwalker81

Member
Local time
Today, 03:40
Joined
Feb 26, 2016
Messages
93
Problem... I went to set this up and had the message 'Cannot join on Memo, OLE, or Hyperlink Object', as of course, [ingredients] is a Long Text field. Back to the drawing board?
 

strive4peace

AWF VIP
Local time
Today, 05:40
Joined
Apr 3, 2020
Messages
1,003
hi Alex,

there shouldn't be any join line between the 2 tables. If one is there, delete it. Try picking specific fields you want to show

SELECT Allergens.FieldName1, Ingredients.FieldName2, Ingredients.FieldName3
FROM Allergens, Ingredients
WHERE Ingredients.FieldName Like "*" & Allergens.FieldName & "*"
 

alexfwalker81

Member
Local time
Today, 03:40
Joined
Feb 26, 2016
Messages
93
hi Alex,

there shouldn't be any join line between the 2 tables. If one is there, delete it. Try picking specific fields you want to show

SELECT Allergens.FieldName1, Ingredients.FieldName2, Ingredients.FieldName3
FROM Allergens, Ingredients
WHERE Ingredients.FieldName Like "*" & Allergens.FieldName & "*"

Working like a charm!
 

strive4peace

AWF VIP
Local time
Today, 05:40
Joined
Apr 3, 2020
Messages
1,003
you're welcome, Alex ~ happy to step in when theDBguy was probably sleeping ;)

please be sure to mark this as SOLVED (link above your first post on the thread)
 

Users who are viewing this thread

Top Bottom