problem with "#"

Prev: Insert Multiple Records into a Table
Next: SQL to export query into table

From: johnlute on 13 Apr 2010 16:03

---

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!

From: Bob Barrows on 13 Apr 2010 16:23

---

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],"#","[#]") & "*"

--
HTH,
Bob Barrows

From: johnlute on 13 Apr 2010 17:12

---

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!

On Apr 13, 4:23 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> 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],"#","[#]") & "*"
>
> --
> HTH,
> Bob Barrows

From: John Spencer on 13 Apr 2010 20:02

---

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

johnlute wrote:
> 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!
>
> On Apr 13, 4:23 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
>> 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],"#","[#]") & "*"
>>
>> --
>> HTH,
>> Bob Barrows
>

From: johnlute on 14 Apr 2010 08:17

---

That did the trick! Thanks, John!!!

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

On Apr 13, 8:02 pm, John Spencer <spen...(a)chpdm.edu> wrote:
> 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
>
>
>
> johnlute wrote:
> > 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!
>
> > On Apr 13, 4:23 pm, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> >> 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],"#","[#]") & "*"
>
> >> --
> >> HTH,
> >> Bob Barrows- Hide quoted text -
>
> - Show quoted text -

 |  Next  |  Last
Pages: 1 2
Prev: Insert Multiple Records into a Table
Next: SQL to export query into table