Ampersand in Query Criteria

  • Thread starter Thread starter AG
  • Start date Start date
A

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.
 
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?
 
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


KARL DEWEY said:
"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.


AG said:
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




.
Click to expand...
Click to expand...
 
AG said:
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.
Click to expand...


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"
 
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

KARL said:
"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?
Click to expand...
 
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.
Click to expand...

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])?
 
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


fredg said:
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.
Click to expand...

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])?
Click to expand...
 
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


John Spencer said:
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

KARL said:
If I type a & b as criteria in a query, Access changes it to "a" & "b".
Click to expand...
"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?
Click to expand...
Click to expand...
 
Thanks Marshall. Makes sense.

--

AG
Email: npATadhdataDOTcom


Marshall Barton said:
AG said:
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.
Click to expand...


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"
Click to expand...
 
Thanks Clifford.

--

AG
Email: npATadhdataDOTcom


Clifford Bass via AccessMonster.com said:
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.
Click to expand...
Click to expand...
 
Back
Top