Query

[Thomas A. Anderson]

I have ran into a rut! I have google'd, Yahoo'd, but could not find the
proper syntax. I have spent over two day trying to figure this out, and
decided that I will have to ask for help from the experts!
I have a table with three columns, LName, FName, Phone that I will be
querying from.
I would like to set a specific criteria for the Phone. I would only like to
extract a specific area code:
I have the following area codes under the Phone column: 212, 315, 347, 516.
I would like only for 315 to show when I do the query.
I have tried using the following syntax : ="315", ="(315)", "315", "(315)",
"315""###-#####", "315 ###-####".
Any ideas on what I am doing wrong?

Access Newbie :(
Thomas

[Rich P]

Re: Query

Hi Thomas,

Here are a few considerations. First, are the phone numbers
consistently being entered as (315) 111-1111? Or are there occasions
where you have 315-111-111, or 315 111-111? If the numbers
(alpha-numeric numbers) are consistently being entered as (315) 111-1111
then you can add criteria like this under the phone field

Like "(315)*"

But if the entries are not consistently (xxx) then using the Like
keyword will drop out a lot of entries. You could try Like "*315*", but
that will pick any number that contains 315 such as (315) 111-7315.

If the area code is key to your project you might consider adding an
areaCode field. This way the user enters the area code in one field and
the rest of the number in the phone field.

One other option, which is a little more sophisticated, is to make a
reference to the Microsoft VBscript Regular Expressions Library (Goto
Tools/References in a Code Module). Then google to get some examples of
using Regular Expressions. Regular Expressions (derived from the Perl
programming language) will restrict the format in which phone numbers
can be entered (and zipcodes, anything). Phone numbers are the most
common restriction usage for Regular expressions. so this way users can
only enter (315) xxx-xxxx. Then you can query phone numbers using Like
"(315*)"

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Danny J. Lesandrini]

Re: Query

Actually, I have no idea what you are doing. What does the pound sign
accomplish in sql?

Have you tried this ...

select * from tblYourTableHer e
where [PhoneNumber] Like '315*'

--

Danny J. Lesandrini
dlesandrini@hot mail.com

http://amazecreations.com/datafast/

"Thomas A. Anderson" <NOSPAM_kayuman @hotmail.com> wrote ...[color=blue]
>I have ran into a rut! I have google'd, Yahoo'd, but could not find the proper syntax. I have spent over two day
>trying to figure this out, and decided that I will have to ask for help from the experts!
> I have a table with three columns, LName, FName, Phone that I will be querying from.
> I would like to set a specific criteria for the Phone. I would only like to extract a specific area code:
> I have the following area codes under the Phone column: 212, 315, 347, 516.
> I would like only for 315 to show when I do the query.
> I have tried using the following syntax : ="315", ="(315)", "315", "(315)", "315""###-#####", "315 ###-####".
> Any ideas on what I am doing wrong?
>
> Access Newbie :(
> Thomas
>
>[/color]

[fredg]

Re: Query

On Wed, 19 Oct 2005 16:16:58 -0700, Thomas A. Anderson wrote:
[color=blue]
> I have ran into a rut! I have google'd, Yahoo'd, but could not find the
> proper syntax. I have spent over two day trying to figure this out, and
> decided that I will have to ask for help from the experts!
> I have a table with three columns, LName, FName, Phone that I will be
> querying from.
> I would like to set a specific criteria for the Phone. I would only like to
> extract a specific area code:
> I have the following area codes under the Phone column: 212, 315, 347, 516.
> I would like only for 315 to show when I do the query.
> I have tried using the following syntax : ="315", ="(315)", "315", "(315)",
> "315""###-#####", "315 ###-####".
> Any ideas on what I am doing wrong?
>
> Access Newbie :(
> Thomas[/color]

Where Left([Phone],3) = "315" or left([Phone],5) = "(315)"
should take care of entries like (313) 123-4567 or 313 123-4567

If you also store phone numbers without the area code, you would also
want to restrict the search to only records where the length of the
Phone data was more than 8 characters:
Where Len([Phone])>8 and (Left([Phone],3) = "315" Or left([Phone],5) =
"(315)")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Thomas A. Anderson]

Re: Query

AWESOME Rich! Many thanks! The first option you mentioned worked: Like
"(315)*".
Could you be kind enough to explain what the difference between Like
"(315)*" and Like "315*" and why Like "(315)" or ="315" did not work.

Again, Thank you!
Thomas

-An Access Newbie and learning a lot!

"Rich P" <rpng123@aol.co m> wrote in message
news:dDB5f.100$ Ne.17590@news.u swest.net...[color=blue]
> Hi Thomas,
>
> Here are a few considerations. First, are the phone numbers
> consistently being entered as (315) 111-1111? Or are there occasions
> where you have 315-111-111, or 315 111-111? If the numbers
> (alpha-numeric numbers) are consistently being entered as (315) 111-1111
> then you can add criteria like this under the phone field
>
> Like "(315)*"
>
> But if the entries are not consistently (xxx) then using the Like
> keyword will drop out a lot of entries. You could try Like "*315*", but
> that will pick any number that contains 315 such as (315) 111-7315.
>
> If the area code is key to your project you might consider adding an
> areaCode field. This way the user enters the area code in one field and
> the rest of the number in the phone field.
>
> One other option, which is a little more sophisticated, is to make a
> reference to the Microsoft VBscript Regular Expressions Library (Goto
> Tools/References in a Code Module). Then google to get some examples of
> using Regular Expressions. Regular Expressions (derived from the Perl
> programming language) will restrict the format in which phone numbers
> can be entered (and zipcodes, anything). Phone numbers are the most
> common restriction usage for Regular expressions. so this way users can
> only enter (315) xxx-xxxx. Then you can query phone numbers using Like
> "(315*)"
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

[Thomas A. Anderson]

Re: Query

Everything that I tried failed. So, I thought maybe that I needed place
holders for the remaining digits.
I tried everything, except for what Rich mentioned (previous post). I am
just starting to learn SQL syntax by doing the QBE GUI and then looking at
the SQL equivalent. But other than that, I am a complete NEWBIE. I have
only been learning MS Access for the last three weeks. But, I am more than
confident that by this time next year, I will be able to help some newbie's
:)

Thank you,
Thomas

-An Access Newbie and learning a lot!


"Danny J. Lesandrini" <dlesandrini@ho tmail.com> wrote in message
news:QKudnal13N LJe8veRVn-qg@comcast.com. ..[color=blue]
> Actually, I have no idea what you are doing. What does the pound sign
> accomplish in sql?
>
> Have you tried this ...
>
> select * from tblYourTableHer e
> where [PhoneNumber] Like '315*'
>
> --
>
> Danny J. Lesandrini
> dlesandrini@hot mail.com
> http://amazecreations.com/datafast/
>
>
>
> "Thomas A. Anderson" <NOSPAM_kayuman @hotmail.com> wrote ...[color=green]
>>I have ran into a rut! I have google'd, Yahoo'd, but could not find the
>>proper syntax. I have spent over two day trying to figure this out, and
>>decided that I will have to ask for help from the experts!
>> I have a table with three columns, LName, FName, Phone that I will be
>> querying from.
>> I would like to set a specific criteria for the Phone. I would only like
>> to extract a specific area code:
>> I have the following area codes under the Phone column: 212, 315, 347,
>> 516.
>> I would like only for 315 to show when I do the query.
>> I have tried using the following syntax : ="315", ="(315)", "315",
>> "(315)", "315""###-#####", "315 ###-####".
>> Any ideas on what I am doing wrong?
>>
>> Access Newbie :(
>> Thomas
>>
>>[/color]
>
>[/color]

[Rich P]

Re: Query

[color=blue][color=green]
>>[/color][/color]
AWESOME Rich! Many thanks! The first option you mentioned worked: Like
"(315)*".
Could you be kind enough to explain what the difference between Like
"(315)*" and Like "315*" and why Like "(315)" or ="315" did not work.

Again, Thank you!
Thomas
<<

The difference between Like "(315)*" and Like "315*" is that with
"(315)*" you are searching for strings that contain 315 within the
parentheses. "315*" will search for any string that begins with 315.
So if your strings all begin with a paren (xxx)... nothing will ever get
picked up. And Like "(315)" will not pick up anything either because
the Like keyword is associated with the wildcard symbol *. Without the
wildcard, Like returns nothing. you can place the * either before
and/or after and string -- "*315*" will pick up anything that contains
the string "315" -- ex: (315) 111-1111, (209) 111-7315.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Thelma Lubkin]

Re: Query

Rich P <rpng123@aol.co m> wrote:

: The difference between Like "(315)*" and Like "315*" is that with
: "(315)*" you are searching for strings that contain 315 within the
: parentheses. "315*" will search for any string that begins with 315.
: So if your strings all begin with a paren (xxx)... nothing will ever get
: picked up. And Like "(315)" will not pick up anything either because
: the Like keyword is associated with the wildcard symbol *. Without the
: wildcard, Like returns nothing.

You mean *in this example* I hope. Otherwise you've confused
me about Like -- I thought that Like "(315)" will pick up the
string if (315) is its entire content, which of course, it
should never be in this example.

--thelma

: HTH

: Rich

[fredg]

Re: Query

On 20 Oct 2005 14:42:42 GMT, Thelma Lubkin wrote:
[color=blue]
> Rich P <rpng123@aol.co m> wrote:
>
>: The difference between Like "(315)*" and Like "315*" is that with
>: "(315)*" you are searching for strings that contain 315 within the
>: parentheses. "315*" will search for any string that begins with 315.
>: So if your strings all begin with a paren (xxx)... nothing will ever get
>: picked up. And Like "(315)" will not pick up anything either because
>: the Like keyword is associated with the wildcard symbol *. Without the
>: wildcard, Like returns nothing.
>
> You mean *in this example* I hope. Otherwise you've confused
> me about Like -- I thought that Like "(315)" will pick up the
> string if (315) is its entire content, which of course, it
> should never be in this example.
>
> --thelma
>
>: HTH
>
>: Rich[/color]

Like "(315)" will return only records that have only (315) in the
field.
Like "(315)*" will return records that begin with (315), such as
(315) 123-4567. However, it will not find records that begin with 315
(without the parenthesis). If the format of the data in the field
varies between (315) and 315 you will need to use an OR operator, as I
suggested in my reply (which also gave you an alternative method,
using Left(), and how to test for just a 7 digit number without area
code).
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail