Selection by non-alphanumeric characters

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl

Hi

How can I select records that have non-alphanumeric characters in a
field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Any way to exclude - (hyphen) from selection?

Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl

Hi

How can I select records that have non-alphanumeric characters in a
field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Any way to use ! (literally) and {space} in the filter?

Many Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl

Hi

How can I select records that have non-alphanumeric characters in a
field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

John wrote:

How can I select records that have non-alphanumeric characters in a field
using a select query?

Set the field's criteria to:

Like "*[!a-z0-9]*"

--
Marsh
MVP [MS Access]

Any idea how I can include {space}, ! (literally) and - (hyphen) in the
criteria as well?

Thanks

Regards

"Marshall Barton" <ma*********@wowway.com> wrote in message
news:ph********************************@4ax.com...

John wrote:

How can I select records that have non-alphanumeric characters in a field
using a select query?

Set the field's criteria to:

Like "*[!a-z0-9]*"

--
Marsh
MVP [MS Access]

! matches itself anywhere except at the start of the [...] character
class.
- matches itself if it is the first or last character in the class (or
the first character after a negating !
a space matches itself in the usual way.

See VBA help on the Like Operator for more.

On Sun, 22 Jan 2006 05:26:20 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Any way to use ! (literally) and {space} in the filter?

Many Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl

Hi

How can I select records that have non-alphanumeric characters in a
field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Dear John:

I strongly recommend you read the online help section on LIKE. It tells you
all about this. Then you'll know just how to do it.

Tom Ellison
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:ul**************@TK2MSFTNGP12.phx.gbl...

Any idea how I can include {space}, ! (literally) and - (hyphen) in the
criteria as well?

Thanks

Regards

"Marshall Barton" <ma*********@wowway.com> wrote in message
news:ph********************************@4ax.com...

John wrote:

How can I select records that have non-alphanumeric characters in a field
using a select query?

Set the field's criteria to:

Like "*[!a-z0-9]*"

--
Marsh
MVP [MS Access]

Thanks. I am now using; Like "*[!-!0-9A-Z &,.@/'():+]*" but it is also
excluding all accented characters. Is there a way that accented characters
are not included in this filter?

Thank again.

Regards
"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:jv********************************@4ax.com...

! matches itself anywhere except at the start of the [...] character
class.
- matches itself if it is the first or last character in the class (or
the first character after a negating !
a space matches itself in the usual way.

See VBA help on the Like Operator for more.

On Sun, 22 Jan 2006 05:26:20 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Any way to use ! (literally) and {space} in the filter?

Many Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl
Hi

How can I select records that have non-alphanumeric characters in a
field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

This is addressed in the help article I referred you to.
On Sun, 22 Jan 2006 19:12:39 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Thanks. I am now using; Like "*[!-!0-9A-Z &,.@/'():+]*" but it is also
excluding all accented characters. Is there a way that accented characters
are not included in this filter?

Thank again.

Regards
"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:jv********************************@4ax.com.. .

! matches itself anywhere except at the start of the [...] character
class.
- matches itself if it is the first or last character in the class (or
the first character after a negating !
a space matches itself in the usual way.

See VBA help on the Like Operator for more.

On Sun, 22 Jan 2006 05:26:20 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Any way to use ! (literally) and {space} in the filter?

Many Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl
> Hi
>
> How can I select records that have non-alphanumeric characters in a
> field using a select query?

WHERE [SomeField] Like "*[!0-9A-Z]*"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Below is what I found in access help. Which part do I need?

Thanks

Regards

Like Operator
Compares a string expression to a pattern in an SQL expression.

Syntax
expression Like "pattern"

The Like operator syntax has these parts:

Part Description
expression SQL expression used in a WHERE clause.
pattern String or character string literal against which expression is
compared.
Remarks
You can use the Like operator to find values in a field that match the
pattern you specify. For pattern, you can specify the complete value (for
example, Like "Smith"), or you can use wildcard characters to find a range
of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to
a string expression. For example, if you enter Like "C*" in an SQL query,
the query returns all field values beginning with the letter C. In a
parameter query, you can prompt the user for a pattern to search for.

The following example returns data that begins with the letter P followed by
any letter between A and F and three digits:

Like "P[A-F]###"
The following table shows how you can use Like to test expressions for
different patterns.
Kind of match
Pattern Match
(returns True) No match
(returns False)
Multiple characters a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Special character a[*]a a*a aaa
Multiple characters ab* abcdefg, abc cab, aab
Single character a?a aaa, a3a, aBa aBBBa
Single digit a#a a0a, a1a, a2a aaa, a10a
Range of characters [a-z] f, p, j 2, &
Outside a range [!a-z] 9, &, % b, a
Not a digit [!0-9] A, a, &, ~ 0, 1, 9
Combined a[!b-m]# An9, az0, a99 abc, aj0
See Also
SQL Expressions Using Wildcard Characters in String Comparisons
WHERE Clause (Microsoft Jet SQL)
Example
Like Operator Example

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:si********************************@4ax.com...

This is addressed in the help article I referred you to.
On Sun, 22 Jan 2006 19:12:39 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Thanks. I am now using; Like "*[!-!0-9A-Z &,.@/'():+]*" but it is also
excluding all accented characters. Is there a way that accented characters
are not included in this filter?

Thank again.

Regards
"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:jv********************************@4ax.com. ..

! matches itself anywhere except at the start of the [...] character
class.
- matches itself if it is the first or last character in the class (or
the first character after a negating !
a space matches itself in the usual way.

See VBA help on the Like Operator for more.

On Sun, 22 Jan 2006 05:26:20 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:
Any way to use ! (literally) and {space} in the filter?

Many Thanks

Regards

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
news:OK*************@TK2MSFTNGP11.phx.gbl...
> "John" <Jo**@nospam.infovis.co.uk> wrote in message
> news:%2****************@TK2MSFTNGP09.phx.gbl
>> Hi
>>
>> How can I select records that have non-alphanumeric characters in a
>> field using a select query?
>
> WHERE [SomeField] Like "*[!0-9A-Z]*"
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Below is what I found in access help. Which part do I need?

Thanks

Regards

Like Operator
Compares a string expression to a pattern in an SQL expression.

Syntax
expression Like "pattern"

The Like operator syntax has these parts:

Part Description
expression SQL expression used in a WHERE clause.
pattern String or character string literal against which expression is
compared.
Remarks
You can use the Like operator to find values in a field that match the
pattern you specify. For pattern, you can specify the complete value (for
example, Like "Smith"), or you can use wildcard characters to find a range
of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to
a string expression. For example, if you enter Like "C*" in an SQL query,
the query returns all field values beginning with the letter C. In a
parameter query, you can prompt the user for a pattern to search for.

The following example returns data that begins with the letter P followed by
any letter between A and F and three digits:

Like "P[A-F]###"
The following table shows how you can use Like to test expressions for
different patterns.
Kind of match
Pattern Match
(returns True) No match
(returns False)
Multiple characters a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Special character a[*]a a*a aaa
Multiple characters ab* abcdefg, abc cab, aab
Single character a?a aaa, a3a, aBa aBBBa
Single digit a#a a0a, a1a, a2a aaa, a10a
Range of characters [a-z] f, p, j 2, &
Outside a range [!a-z] 9, &, % b, a
Not a digit [!0-9] A, a, &, ~ 0, 1, 9
Combined a[!b-m]# An9, az0, a99 abc, aj0
See Also
SQL Expressions Using Wildcard Characters in String Comparisons
WHERE Clause (Microsoft Jet SQL)
Example
Like Operator Example

"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:u$**************@TK2MSFTNGP14.phx.gbl...

Dear John:

I strongly recommend you read the online help section on LIKE. It tells
you all about this. Then you'll know just how to do it.

Tom Ellison
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:ul**************@TK2MSFTNGP12.phx.gbl...

Any idea how I can include {space}, ! (literally) and - (hyphen) in the
criteria as well?

Thanks

Regards

"Marshall Barton" <ma*********@wowway.com> wrote in message
news:ph********************************@4ax.com...

John wrote:
How can I select records that have non-alphanumeric characters in a
field
using a select query?
Set the field's criteria to:

Like "*[!a-z0-9]*"

--
Marsh
MVP [MS Access]

I think we must be looking at different versions of Access. I was
referring to the help topic for the VBA Like operator , which describes
how Like's treatment of accented characters depends on the setting of
Option Compare and on the current Locale. The wording is virtually the
same in Access 97 and Access 2003, and but how you get to the article is
different between two versions.

But it's silly to have two threads on the same question running at the
same time. I'm going to stop posting in this one.
On Tue, 24 Jan 2006 02:04:40 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Below is what I found in access help. Which part do I need?

Thanks

Regards

Like Operator
Compares a string expression to a pattern in an SQL expression.

Syntax
expression Like "pattern"

The Like operator syntax has these parts:

Part Description
expression SQL expression used in a WHERE clause.
pattern String or character string literal against which expression is
compared.
Remarks
You can use the Like operator to find values in a field that match the
pattern you specify. For pattern, you can specify the complete value (for
example, Like "Smith"), or you can use wildcard characters to find a range
of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to
a string expression. For example, if you enter Like "C*" in an SQL query,
the query returns all field values beginning with the letter C. In a
parameter query, you can prompt the user for a pattern to search for.

The following example returns data that begins with the letter P followed by
any letter between A and F and three digits:

Like "P[A-F]###"
The following table shows how you can use Like to test expressions for
different patterns.
Kind of match
Pattern Match
(returns True) No match
(returns False)
Multiple characters a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Special character a[*]a a*a aaa
Multiple characters ab* abcdefg, abc cab, aab
Single character a?a aaa, a3a, aBa aBBBa
Single digit a#a a0a, a1a, a2a aaa, a10a
Range of characters [a-z] f, p, j 2, &
Outside a range [!a-z] 9, &, % b, a
Not a digit [!0-9] A, a, &, ~ 0, 1, 9
Combined a[!b-m]# An9, az0, a99 abc, aj0
See Also
SQL Expressions Using Wildcard Characters in String Comparisons
WHERE Clause (Microsoft Jet SQL)
Example
Like Operator Example

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:si********************************@4ax.com.. .

This is addressed in the help article I referred you to.
On Sun, 22 Jan 2006 19:12:39 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

Thanks. I am now using; Like "*[!-!0-9A-Z &,.@/'():+]*" but it is also
excluding all accented characters. Is there a way that accented characters
are not included in this filter?

Thank again.

Regards
"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:jv********************************@4ax.com ...
! matches itself anywhere except at the start of the [...] character
class.
- matches itself if it is the first or last character in the class (or
the first character after a negating !
a space matches itself in the usual way.

See VBA help on the Like Operator for more.

On Sun, 22 Jan 2006 05:26:20 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

>
>Any way to use ! (literally) and {space} in the filter?
>
>Many Thanks
>
>Regards
>
>"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
>news:OK*************@TK2MSFTNGP11.phx.gbl.. .
>> "John" <Jo**@nospam.infovis.co.uk> wrote in message
>> news:%2****************@TK2MSFTNGP09.phx.gbl
>>> Hi
>>>
>>> How can I select records that have non-alphanumeric characters in a
>>> field using a select query?
>>
>> WHERE [SomeField] Like "*[!0-9A-Z]*"
>>
>> --
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>>
>>
>
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.