querying a concatenated value

[johnlute]

Access 2003.

I wasn't sure where to post this because it involves a form and query.
This seemed more the appropriate forum.

I have a form [Main Menu] which has two controls [cbAllergen] and
[cbSensitivity]. I use these controls to filter a report which has
this record source:

SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster LEFT JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) LEFT JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND ((qryINGsSensitivities.Sensitivities) Like
[Forms]![Main Menu]![cbSensitivity] & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like [Forms]![Main Menu]!
[cbSensitivity] & "*") AND (([Forms]![Main Menu]![cbAllergen]) Is
Null)) OR (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND (([Forms]![Main Menu]![cbSensitivity]) Is
Null)) OR ((([Forms]![Main Menu]![cbAllergen]) Is Null) AND (([Forms]!
[Main Menu]![cbSensitivity]) Is Null));

The two queries [qryINGsAllergens] and [qryINGsSensitivities]
concatenate lke this:

SELECT IngredientMaster.IMNumber, Concatenate("SELECT INGsAllergens
FROM tblINGsAllergens WHERE IMNumber =" & [IMNumber] & " ORDER BY
INGsAllergens") AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));

Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"

Here's the progblem: If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. Clearly, because Access is looking
strictly for the first value in the concatenation. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.

How can I re-configure this to resolve the problem?

Thanks for your time!

 

[Tom van Stiphout]

On Wed, 7 Apr 2010 05:43:06 -0700 (PDT), johnlute <[email protected]>
wrote:

Wildcards work like this: where the * is can be any number of
characters. So if I search for:
FirstName like 'T*'
then Tom, Tim, Tanya, etc. are found.

If I search for:
FirstName like '*T*'
then any name with a T in any position will be found.

Similarly '*T' would find names with a T at the end.

Armed with this knowledge I'm sure you can fix your query.

-Tom.
Microsoft Access MVP

 

[johnlute]

Thanks for the ammo, Tom!

Wildcards work like this: where the * is can be any number of
characters. So if I search for:
FirstName like 'T*'
then Tom, Tim, Tanya, etc. are found.

If I search for:
FirstName like '*T*'
then any name with a T in any position will be found.

Similarly '*T' would find names with a T at the end.

Armed with this knowledge I'm sure you can fix your query.

-Tom.
Microsoft Access MVP

Access 2003.

I wasn't sure where to post this because it involves a form and query.
This seemed more the appropriate forum.

I have a form [Main Menu] which has two controls [cbAllergen] and
[cbSensitivity]. I use these controls to filter a report which has
this record source:

SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster LEFT JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) LEFT JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND ((qryINGsSensitivities.Sensitivities) Like
[Forms]![Main Menu]![cbSensitivity] & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like [Forms]![Main Menu]!
[cbSensitivity] & "*") AND (([Forms]![Main Menu]![cbAllergen]) Is
Null)) OR (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND (([Forms]![Main Menu]![cbSensitivity]) Is
Null)) OR ((([Forms]![Main Menu]![cbAllergen]) Is Null) AND (([Forms]!
[Main Menu]![cbSensitivity]) Is Null));

The two queries [qryINGsAllergens] and [qryINGsSensitivities]
concatenate lke this:

SELECT IngredientMaster.IMNumber, Concatenate("SELECT INGsAllergens
FROM tblINGsAllergens  WHERE IMNumber =" & [IMNumber] & " ORDER BY
INGsAllergens") AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));

Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"

Here's the progblem: If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. Clearly, because Access is looking
strictly for the first value in the concatenation. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.

How can I re-configure this to resolve the problem?

Thanks for your time!- Hide quoted text -

- Show quoted text -

 

[johnlute]

Hi, Tom.

Upon more testing to your suggestion I found that I'm still in
trouble. I've tried:
Like [Forms]![Main Menu]![cbAllergen] & "*"
Like [Forms]![Main Menu]![cbAllergen] & "*.*"
Like [Forms]![Main Menu]![cbAllergen] & "**"

None of these find the value that I'm trying to find in the
concatenated value. The above all result in the same problem:
Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"

If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.

Any other ideas?

Thanks!

Wildcards work like this: where the * is can be any number of
characters. So if I search for:
FirstName like 'T*'
then Tom, Tim, Tanya, etc. are found.

If I search for:
FirstName like '*T*'
then any name with a T in any position will be found.

Similarly '*T' would find names with a T at the end.

Armed with this knowledge I'm sure you can fix your query.

-Tom.
Microsoft Access MVP

Access 2003.

I wasn't sure where to post this because it involves a form and query.
This seemed more the appropriate forum.

I have a form [Main Menu] which has two controls [cbAllergen] and
[cbSensitivity]. I use these controls to filter a report which has
this record source:

SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster LEFT JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) LEFT JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND ((qryINGsSensitivities.Sensitivities) Like
[Forms]![Main Menu]![cbSensitivity] & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like [Forms]![Main Menu]!
[cbSensitivity] & "*") AND (([Forms]![Main Menu]![cbAllergen]) Is
Null)) OR (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND (([Forms]![Main Menu]![cbSensitivity]) Is
Null)) OR ((([Forms]![Main Menu]![cbAllergen]) Is Null) AND (([Forms]!
[Main Menu]![cbSensitivity]) Is Null));

The two queries [qryINGsAllergens] and [qryINGsSensitivities]
concatenate lke this:

SELECT IngredientMaster.IMNumber, Concatenate("SELECT INGsAllergens
FROM tblINGsAllergens  WHERE IMNumber =" & [IMNumber] & " ORDER BY
INGsAllergens") AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));

Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"

Here's the progblem: If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. Clearly, because Access is looking
strictly for the first value in the concatenation. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.

How can I re-configure this to resolve the problem?

Thanks for your time!- Hide quoted text -

- Show quoted text -

 

[johnlute]

Pleas disregard. I got it:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"

I'm a little slow...

Hi, Tom.

Upon more testing to your suggestion I found that I'm still in
trouble. I've tried:
Like [Forms]![Main Menu]![cbAllergen] & "*"
Like [Forms]![Main Menu]![cbAllergen] & "*.*"
Like [Forms]![Main Menu]![cbAllergen] & "**"

None of these find the value that I'm trying to find in the
concatenated value. The above all result in the same problem:
Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"

If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.

Any other ideas?

Thanks!

Wildcards work like this: where the * is can be any number of
characters. So if I search for:
FirstName like 'T*'
then Tom, Tim, Tanya, etc. are found.

If I search for:
FirstName like '*T*'
then any name with a T in any position will be found.

Similarly '*T' would find names with a T at the end.

Armed with this knowledge I'm sure you can fix your query.

-Tom.
Microsoft Access MVP

Access 2003.
I wasn't sure where to post this because it involves a form and query.
This seemed more the appropriate forum.
I have a form [Main Menu] which has two controls [cbAllergen] and
[cbSensitivity]. I use these controls to filter a report which has
this record source:
SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster LEFT JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) LEFT JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND ((qryINGsSensitivities.Sensitivities) Like
[Forms]![Main Menu]![cbSensitivity] & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like [Forms]![Main Menu]!
[cbSensitivity] & "*") AND (([Forms]![Main Menu]![cbAllergen]) Is
Null)) OR (((qryINGsAllergens.Allergens) Like [Forms]![Main Menu]!
[cbAllergen] & "*") AND (([Forms]![Main Menu]![cbSensitivity]) Is
Null)) OR ((([Forms]![Main Menu]![cbAllergen]) Is Null) AND (([Forms]!
[Main Menu]![cbSensitivity]) Is Null));
The two queries [qryINGsAllergens] and [qryINGsSensitivities]
concatenate lke this:
SELECT IngredientMaster.IMNumber, Concatenate("SELECT INGsAllergens
FROM tblINGsAllergens  WHERE IMNumber =" & [IMNumber] & " ORDER BY
INGsAllergens") AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));
Let's say that [IMNumber] 1 has this concatenated allergen value:
"Egg, Fish, Milk"
Here's the progblem: If I select "Milk" in [Main Menu].[cbAllergen]
then the report returns Null. Clearly, because Access is looking
strictly for the first value in the concatenation. If I select "Egg"
in [Main Menu].[cbAllergen] then the report returns "Egg, Fish, Milk"
as desired.
How can I re-configure this to resolve the problem?
Thanks for your time!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -