Selection by non-alphanumeric characters

[John]

Hi

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

Thanks

Regards

 

[Dirk Goldgar]

Hi

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

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

 

[John]

Any way to exclude - (hyphen) from selection?

Thanks

Regards

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]

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

Many Thanks

Regards

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)

 

[Marshall Barton]

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

 

[John]

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

Thanks

Regards

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

 

[John Nurick]

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

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

Many Thanks

Regards

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)

 

[Tom Ellison]

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

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

Thanks

Regards

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

 

[John]

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

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

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

Many Thanks

Regards

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]

This is addressed in the help article I referred you to.

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

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

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

Many Thanks

Regards

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]

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

This is addressed in the help article I referred you to.

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

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

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

Many Thanks

Regards

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]

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

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

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

Thanks

Regards

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

 

[John Nurick]

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.

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

This is addressed in the help article I referred you to.

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


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

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

Many Thanks

Regards

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)