problem with "#"

[johnlute]

Access 2003.

I have this criteria:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"

This criteria is used to find values in a concatenated string.

It works fine EXCEPT if something like "Yellow #5" is in the
concatenated string. I've tested it and it appears that the "#" is
what's frsutrating Access.

Does anyone know a way to revise the criteria to include the "#"
character?

Thanks for your help!

 

[Bob Barrows]

Access 2003.

I have this criteria:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"

This criteria is used to find values in a concatenated string.

It works fine EXCEPT if something like "Yellow #5" is in the
concatenated string. I've tested it and it appears that the "#" is
what's frsutrating Access.

Does anyone know a way to revise the criteria to include the "#"
character?

You need to escape it by enclosing it with brackets using the Replace
function:

Like "*" & Replace([Forms]![Main Menu]![cbAllergen],"#","[#]") & "*"

 

[johnlute]

Hi, Bob.

That's new to me! I give it a whirl but it returns "Typed incorrectly,
or too complex, etc...." I copied/pasted your code exactly but my
complete SQL is perhaps too complicated:
SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster INNER JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) INNER JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like "*" & Replace([Forms]![Main
Menu]![cbAllergen],"#","[#]") & "*") AND
((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]![Main
Menu]![cbSensitivity],"#","[#]") & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]!
[Main Menu]![cbSensitivity],"#","[#]") & "*") AND (([Forms]![Main
Menu]![cbAllergen]) Is Null)) OR (((qryINGsAllergens.Allergens) Like
"*" & Replace([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));

I tried reducing the query by removing the Null's and it worked but
this isn't going to do it for me.

Any other ways to take care of "#"?

Thanks!

Access 2003.

I have this criteria:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"

This criteria is used to find values in a concatenated string.

It works fine EXCEPT if something like "Yellow #5" is in the
concatenated string. I've tested it and it appears that the "#" is
what's frsutrating Access.

Does anyone know a way to revise the criteria to include the "#"
character?

You need to escape it by enclosing it with brackets using the Replace
function:

Like "*" & Replace([Forms]![Main Menu]![cbAllergen],"#","[#]") & "*"

 

[John Spencer]

Try rewriting it this way.

WHERE (((qryINGsAllergens.Allergens)
Like "*" & Replace("" & [Forms]![Main Menu]![cbAllergen],"#","[#]") & "*")
AND ((qryINGsSensitivities.Sensitivities)
Like "*" & Replace("" & [Forms]![Main Menu]![cbSensitivity],"#","[#]") & "*"))
OR (((qryINGsSensitivities.Sensitivities)
Like "*" & Replace("" & [Forms]![Main Menu]![cbSensitivity],"#","[#]") & "*")
AND (([Forms]![Main Menu]![cbAllergen]) Is Null))
OR (((qryINGsAllergens.Allergens)
Like "*" & Replace("" & [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));

Replace needs a string as the first argument. By appending "" to the
controls' values you are forcing the null to be a string and eliminating the
error that gets generated.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, Bob.

That's new to me! I give it a whirl but it returns "Typed incorrectly,
or too complex, etc...." I copied/pasted your code exactly but my
complete SQL is perhaps too complicated:
SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster INNER JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) INNER JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like "*" & Replace([Forms]![Main
Menu]![cbAllergen],"#","[#]") & "*") AND
((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]![Main
Menu]![cbSensitivity],"#","[#]") & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]!
[Main Menu]![cbSensitivity],"#","[#]") & "*") AND (([Forms]![Main
Menu]![cbAllergen]) Is Null)) OR (((qryINGsAllergens.Allergens) Like
"*" & Replace([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));

I tried reducing the query by removing the Null's and it worked but
this isn't going to do it for me.

Any other ways to take care of "#"?

Thanks!

Access 2003.
I have this criteria:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"
This criteria is used to find values in a concatenated string.
It works fine EXCEPT if something like "Yellow #5" is in the
concatenated string. I've tested it and it appears that the "#" is
what's frsutrating Access.
Does anyone know a way to revise the criteria to include the "#"
character?

You need to escape it by enclosing it with brackets using the Replace
function:

Like "*" & Replace([Forms]![Main Menu]![cbAllergen],"#","[#]") & "*"

 

[johnlute]

That did the trick! Thanks, John!!!

But why does Access have a problem with "#"? Are there any other
characters that might cause a hiccup?

Try rewriting it this way.

WHERE (((qryINGsAllergens.Allergens)
Like "*" & Replace("" & [Forms]![Main Menu]![cbAllergen],"#","[#]") & "*")
AND ((qryINGsSensitivities.Sensitivities)
Like "*" & Replace("" & [Forms]![Main Menu]![cbSensitivity],"#","[#]") & "*"))
OR (((qryINGsSensitivities.Sensitivities)
Like "*" & Replace("" & [Forms]![Main Menu]![cbSensitivity],"#","[#]") & "*")
AND (([Forms]![Main Menu]![cbAllergen]) Is Null))
OR (((qryINGsAllergens.Allergens)
Like "*" & Replace("" & [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));

Replace needs a string as the first argument.  By appending "" to the
controls' values you are forcing the null to be a string and eliminating the
error that gets generated.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi, Bob.

That's new to me! I give it a whirl but it returns "Typed incorrectly,
or too complex, etc...." I copied/pasted your code exactly but my
complete SQL is perhaps too complicated:
SELECT IngredientMaster.IMNumber, IngredientMaster.IMDescription,
qryINGsAllergens.Allergens, qryINGsSensitivities.Sensitivities
FROM (IngredientMaster INNER JOIN qryINGsAllergens ON
IngredientMaster.IMNumber = qryINGsAllergens.IMNumber) INNER JOIN
qryINGsSensitivities ON IngredientMaster.IMNumber =
qryINGsSensitivities.IMNumber
WHERE (((qryINGsAllergens.Allergens) Like "*" & Replace([Forms]![Main
Menu]![cbAllergen],"#","[#]") & "*") AND
((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]![Main
Menu]![cbSensitivity],"#","[#]") & "*")) OR
(((qryINGsSensitivities.Sensitivities) Like "*" & Replace([Forms]!
[Main Menu]![cbSensitivity],"#","[#]") & "*") AND (([Forms]![Main
Menu]![cbAllergen]) Is Null)) OR (((qryINGsAllergens.Allergens) Like
"*" & Replace([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));

I tried reducing the query by removing the Null's and it worked but
this isn't going to do it for me.

Any other ways to take care of "#"?

Thanks!

johnlute wrote:
Access 2003.
I have this criteria:
Like "*" & [Forms]![Main Menu]![cbAllergen] & "*"
This criteria is used to find values in a concatenated string.
It works fine EXCEPT if something like "Yellow #5" is in the
concatenated string. I've tested it and it appears that the "#" is
what's frsutrating Access.
Does anyone know a way to revise the criteria to include the "#"
character?
You need to escape it by enclosing it with brackets using the Replace
function:
Like "*" & Replace([Forms]![Main Menu]![cbAllergen],"#","[#]") & "*"

- Show quoted text -

 

[John W. Vinson]

But why does Access have a problem with "#"? Are there any other
characters that might cause a hiccup?

It's one of quite a few wildcard characters. * means "Any string of
characters", ? means "any single character", # means "any digit 0-9", and so
on. Since your query used the LIKE operator, it was looking for "Yellow 05" or
"Yellow 55" or "Yellow <some other digit>5". Enclosing the # in square
brackets tells Access to treat it as a literal instead of as a wildcard.

 

[johnlute]

Thanks for the clarification, John. I don't do much in the way of
these kinds of searches so I had no idea that there were so many
wildcard characters. This is definitely something to file away for
future reference. Thanks!