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.

--

AG
Email: npATadhdataDOTcom

[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 [b]
would be correct.

What is your complete proposed criteria? Explain it some?

--
Build a little, test a little.

"AG" wrote:

> .
>

[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

"KARL DEWEY" <KARL...@discussions.microsoft.com> wrote in message
news:EFB32079-E194-4174...@microsoft.com...

[Marshall Barton]

AG wrote:

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"

--
Marsh
MVP [MS Access]

[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

[fredg]

On Thu, 7 Jan 2010 14:04:29 -0500, AG wrote:

> 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])?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[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

--
Message posted via http://www.accessmonster.com

[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

"fredg" <fgut...@example.invalid> wrote in message
news:78fo1bmjhvhh.xrpsf59coq10$.dlg@40tude.net...

[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

"John Spencer" <spe...@chpdm.edu> wrote in message
news:ezRVc58j...@TK2MSFTNGP02.phx.gbl...

[AG]

Thanks Marshall. Makes sense.

--

AG
Email: npATadhdataDOTcom

"Marshall Barton" <marsh...@wowway.com> wrote in message
news:tvbck5tok7vagu49b...@4ax.com...

[AG]

Thanks Clifford.

--

AG
Email: npATadhdataDOTcom

"Clifford Bass via AccessMonster.com" <u48370@uwe> wrote in message
news:a1c49a448afb4@uwe...

[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 wrote:
>Thanks Clifford.

[AG]

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

--

AG
Email: npATadhdataDOTcom

"Clifford Bass via AccessMonster.com" <u48370@uwe> wrote in message

news:a1c5089482a02@uwe...