Hi
How can I select records that have non-alphanumeric characters in a field
using a select query?
Thanks
Regards 12 17068
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Oren |
last post by:
Hi,
Is there any way that calls to sp_addrolemember and sp_droprolemember
can be enabled for non database owners and non sysadmin members?
This would be very helpful for an application I'm in...
|
by: Erik Max Francis |
last post by:
I was interesting in adding selection and hit testing to ZOE, and was
looking at the PyOpenGL wrappers' handling of selection and picking. I
see glSelectBuffer to specify the size of the buffer,...
|
by: Hector |
last post by:
I have a ComboBox set up in a non-modal form. When a selection is made from the ComboBox, the handler code closes the form, but then the system crashes because of an unhandled NullReferenceException....
|
by: Andrew |
last post by:
I get a Null Reference Exception if I close a non-modal dialog (that is, a
form opened with Show()) when a selection is made from a ComboBox. The error
message refers to Unsafe Native Methods, but...
|
by: Paul |
last post by:
Hi
I have a page broken into two sections, the first top section is selection
criteria for my datagrid. The second section is the actual datagrid. I
would like the selection criteria section...
|
by: Michel Lapointe |
last post by:
Hello,
I would like to know if there is an event for the Datagridview control
that is trigger before the selection change and is cancelable.
I'm currently using the SelectionChanged event...
|
by: Xah Lee |
last post by:
Of interest:
• The Semicolon Wars, by Brian Hayes. 2006.
http://www.americanscientist.org/template/AssetDetail/assetid/51982
in conjunction to this article, i recommend:
• Software...
|
by: Giovanni Bajo |
last post by:
Hello,
I just read this mail by Brett Cannon:
http://mail.python.org/pipermail/python-dev/2006-October/069139.html
where the "PSF infrastracture committee", after weeks of evaluation, recommends...
|
by: ivowel |
last post by:
Dear Experts: I am trying to write a css style that makes text
invisible; visible-when-selected (so that at least a visible block
appears over the selected text---like black foreground over...
|
by: sialater |
last post by:
Hello,
I realise there are a lot of topics related to this problem but many of what I have found has run cold or unresolved. What I have is an addressbook clone where there are groups which have...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |