alexfwalker81
Member
- Local time
- Today, 07:06
- 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?
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?