Ampersand in Query Criteria

[AG]

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing "a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a query.

 

[KARL DEWEY]

If I type a & b as criteria in a query, Access changes it to "a" & "b".
"a & b" as criteria is not correct syntax.

Is 'a' and 'b' field names or something else?

If you are using the ampersand to mean that both elements of criteria must
be satisfied the you need to use the logical 'AND' instead - [a] AND
would be correct.

What is your complete proposed criteria? Explain it some?

 

[AG]

Karl,

Thanks for the quick response.
No field name involved. This is actual criteria to filter a query.
I would like the actual criteria to be "Mary & James Smith" when I type in
the criteria line, Mary & James Smith.
Access currently changes it to "Mary" & "James Smith".

As I mentioned, this is a simplified example, so what I am looking for is a
way to escape the ampersand, so Access does not consider it a concatenation
operator.
If that is not possible, I will need to rewrite my code to work around the
problem.

--

AG
Email: npATadhdataDOTcom

"a & b" as criteria is not correct syntax.

Is 'a' and 'b' field names or something else?

If you are using the ampersand to mean that both elements of criteria must
be satisfied the you need to use the logical 'AND' instead - [a] AND
would be correct.

What is your complete proposed criteria? Explain it some?

--
Build a little, test a little.

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing
"a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a
query.

--

AG
Email: npATadhdataDOTcom




.

 

[Marshall Barton]

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing "a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a query.

Another reason why the query designer leads people to wrong
conclusions. In this case you should always use quotes
around a string regardless of the characters in the string:
"a & b"

Your "usual method of wrapping in brackets" is only valid
when the criteria uses the Like operator as in:
Like "A#B"
where the # wildcard will match any decimal digit. So, if
you want to match the # character you would use:
Like "A[#]B"

 

[John Spencer]

What you need to do is enclose the argument in quotes so the BuildCriteria
function will know you mean to treat the input string as a string

StrIN = Chr(34) & "A & B" & Chr(34)

BuildCriteria("SomeField",1,StrIN)

BuildCriteria("SomeField",1,"""A & B""") returns
SomeField="A & B"

The problem is that "A & B" as a string is ambiguous and - depending on your
point of view - BuildCriteria function improperly (or properly) handles the
situation. There are other situations that can cause the same problem. For
instance, Aark(B) as a string ends up being treated as if Aark were a function
and B is an argument to the function so you get
SomeField = Aark("B")

One more example Aark.ccf gets treated as a reference to a table and a field
in the table.
SomeField = [Aark].[CCF]

Safest thing to do is to wrap any thing you believe is a string in quotes
using one of the two methods shown above.

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

"a & b" as criteria is not correct syntax.

Is 'a' and 'b' field names or something else?

If you are using the ampersand to mean that both elements of criteria must
be satisfied the you need to use the logical 'AND' instead - [a] AND
would be correct.

What is your complete proposed criteria? Explain it some?

 

[fredg]

Karl,

Thanks for the quick response.
No field name involved. This is actual criteria to filter a query.
I would like the actual criteria to be "Mary & James Smith" when I type in
the criteria line, Mary & James Smith.
Access currently changes it to "Mary" & "James Smith".

As I mentioned, this is a simplified example, so what I am looking for is a
way to escape the ampersand, so Access does not consider it a concatenation
operator.
If that is not possible, I will need to rewrite my code to work around the
problem.

If you are going to hard code the criteria you need to enclose the
entire criteria name within one set of quotes.
Instead of writing
Mary & James Smith
write
"Mary & James Smith"

A better method would be to have Access prompt you for the names so
that you don't have to open the query in Design View each time you
wish a different couple.
As criteria, write:
[Enter the Names]

Now when prompted, all you need do is enter, without quotes,
Mary & James Smith

Which brings us to the $64 question.
Why are you having one field with multiple names in it instead of 3
separate Fields (for [FirstName], [SpouseName], and [Lastname])?

 

[Clifford Bass via AccessMonster.com]

Hi,

Try this:

Dim strCriteria As String

strCriteria = "Mary & John Smith"
Debug.Print Application.BuildCriteria("The_Field", _
DAO.DataTypeEnum.dbText, """" & Replace(strCriteria, """", """""") &
"""")

strCriteria = "Mary & ""John"" Smith"
Debug.Print Application.BuildCriteria("The_Field", _
DAO.DataTypeEnum.dbText, """" & Replace(strCriteria, """", """""") &
"""")

Clifford Bass

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing "a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a query.

 

[AG]

Fred,

Thanks for the response, but as I mentioned, the query example was only a
simplification of the problem that was easily demonstrated. I don't have
multiple names in one field, nor do I use query prompts.

--

AG
Email: npATadhdataDOTcom

Karl,

Thanks for the quick response.
No field name involved. This is actual criteria to filter a query.
I would like the actual criteria to be "Mary & James Smith" when I type
in
the criteria line, Mary & James Smith.
Access currently changes it to "Mary" & "James Smith".

As I mentioned, this is a simplified example, so what I am looking for is
a
way to escape the ampersand, so Access does not consider it a
concatenation
operator.
If that is not possible, I will need to rewrite my code to work around
the
problem.

If you are going to hard code the criteria you need to enclose the
entire criteria name within one set of quotes.
Instead of writing
Mary & James Smith
write
"Mary & James Smith"

A better method would be to have Access prompt you for the names so
that you don't have to open the query in Design View each time you
wish a different couple.
As criteria, write:
[Enter the Names]

Now when prompted, all you need do is enter, without quotes,
Mary & James Smith

Which brings us to the $64 question.
Why are you having one field with multiple names in it instead of 3
separate Fields (for [FirstName], [SpouseName], and [Lastname])?

 

[AG]

Thanks for the good explanation John. In digging into my code, I saw that I
had previously run into the problem with parens and had used the same
solution that you suggested. Guess I just didn't look hard enough before
posting.

--

AG
Email: npATadhdataDOTcom

What you need to do is enclose the argument in quotes so the BuildCriteria
function will know you mean to treat the input string as a string

StrIN = Chr(34) & "A & B" & Chr(34)

BuildCriteria("SomeField",1,StrIN)

BuildCriteria("SomeField",1,"""A & B""") returns
SomeField="A & B"

The problem is that "A & B" as a string is ambiguous and - depending on
your point of view - BuildCriteria function improperly (or properly)
handles the situation. There are other situations that can cause the same
problem. For instance, Aark(B) as a string ends up being treated as if
Aark were a function and B is an argument to the function so you get
SomeField = Aark("B")

One more example Aark.ccf gets treated as a reference to a table and a
field in the table.
SomeField = [Aark].[CCF]

Safest thing to do is to wrap any thing you believe is a string in quotes
using one of the two methods shown above.

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

If I type a & b as criteria in a query, Access changes it to "a" & "b".

"a & b" as criteria is not correct syntax.

Is 'a' and 'b' field names or something else?

If you are using the ampersand to mean that both elements of criteria
must be satisfied the you need to use the logical 'AND' instead - [a]
AND would be correct.

What is your complete proposed criteria? Explain it some?

 

[AG]

Thanks Marshall. Makes sense.

--

AG
Email: npATadhdataDOTcom

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing
"a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a
query.

Another reason why the query designer leads people to wrong
conclusions. In this case you should always use quotes
around a string regardless of the characters in the string:
"a & b"

Your "usual method of wrapping in brackets" is only valid
when the criteria uses the Like operator as in:
Like "A#B"
where the # wildcard will match any decimal digit. So, if
you want to match the # character you would use:
Like "A[#]B"

 

[AG]

Thanks Clifford.

--

AG
Email: npATadhdataDOTcom

Hi,

Try this:

Dim strCriteria As String

strCriteria = "Mary & John Smith"
Debug.Print Application.BuildCriteria("The_Field", _
DAO.DataTypeEnum.dbText, """" & Replace(strCriteria, """", """""")
&
"""")

strCriteria = "Mary & ""John"" Smith"
Debug.Print Application.BuildCriteria("The_Field", _
DAO.DataTypeEnum.dbText, """" & Replace(strCriteria, """", """""")
&
"""")

Clifford Bass

Access 2000 through 2007.

How can I escape the ampersand used in query criteria?
If I type a & b as criteria in a query, Access changes it to "a" & "b".
The usual method of wrapping in brackets results in "a [" & "] b".

This is a simplified example to demonstrate the problem, so simply typing
"a
& b" is not the answer.
What I am actually doing it applying user input to
Application.BuildCriteria, in order to generate a where clause for a
query.

 

[Clifford Bass via AccessMonster.com]

Hi AG,

You are welcome. It is important to include the use of the Replace()
function if the criteria can ever include a quote symbol, even accidentally.
Which would be the case if someone is typing it in.

Clifford Bass

 

[AG]

Thanks Clifford. Quotes are covered. I ran into that some time ago.