Update query using Left function

[faxylady]

I am trying to update some area codes and want to use the Left function.
What I want is simply to be able to identify the area code so that it may be
deleted, used in a make table query, etc. According to Special Edition Using
Access 2000, by Roger Jennings, page 335, the following should work:

Left ("phone",3)=999

However, when I try to use this in an Update query in the criteria row, the
following message is returned to me. The Expression you entered has a
function containing the wrong number of arguments.

Also, I have found that when I actually update a phone number with an area
code change and I use the following, I don't have any problems.

In the Update row: 777&right([phone],8)

the area code is updated to 777 just fine. 8 has to be used because most of
the phone numbers are formated with a dash between the exchange and last four
digits. Notice the ampersand after 777.

What I am also trying to do is isolate area codes in this way:

Left ("phone",3)=999,222,555. These are several area codes in one
metropolitan area.

What is the correct code to use so that I won't receive these syntax errors?

Then I can do a make table query of the isolated area codes or a delete
query or if necessary, an update query.

 

[Ofer Cohen]

Yours
Left ("phone",3)=999

Try
Left ([phone],3) = "999"
=========================
Yours
Left ("phone",3)=999,222,555

Try
Left ([phone],3) In ("999","222","555")
=========================
The field name need to be with square brackets, instead of duble quote.

 

[faxylady]

Thank you kindly. But why does Roger Jennings book, "Special Edition Using
Access 2000," have them in quotes?

However, your answer is a big help.

Yours
Left ("phone",3)=999

Try
Left ([phone],3) = "999"
=========================
Yours
Left ("phone",3)=999,222,555

Try
Left ([phone],3) In ("999","222","555")
=========================
The field name need to be with square brackets, instead of duble quote.

--
Good Luck
BS"D

I am trying to update some area codes and want to use the Left function.
What I want is simply to be able to identify the area code so that it may be
deleted, used in a make table query, etc. According to Special Edition Using
Access 2000, by Roger Jennings, page 335, the following should work:

Left ("phone",3)=999

However, when I try to use this in an Update query in the criteria row, the
following message is returned to me. The Expression you entered has a
function containing the wrong number of arguments.

Also, I have found that when I actually update a phone number with an area
code change and I use the following, I don't have any problems.

In the Update row: 777&right([phone],8)

the area code is updated to 777 just fine. 8 has to be used because most of
the phone numbers are formated with a dash between the exchange and last four
digits. Notice the ampersand after 777.

What I am also trying to do is isolate area codes in this way:

Left ("phone",3)=999,222,555. These are several area codes in one
metropolitan area.

What is the correct code to use so that I won't receive these syntax errors?

Then I can do a make table query of the isolated area codes or a delete
query or if necessary, an update query.

 

[Ofer Cohen]

I didn't read Roger Jennings book, but it could be that in the example they
wanted to show how to retrieve the three left chr of a string

Left("ABCDE",3)
will return "ABC"

but to refer to another field you need to put the name of the field in
square brackets

--
Good Luck
BS"D

Thank you kindly. But why does Roger Jennings book, "Special Edition Using
Access 2000," have them in quotes?

However, your answer is a big help.

Yours
Left ("phone",3)=999

Try
Left ([phone],3) = "999"
=========================
Yours
Left ("phone",3)=999,222,555

Try
Left ([phone],3) In ("999","222","555")
=========================
The field name need to be with square brackets, instead of duble quote.

--
Good Luck
BS"D

I am trying to update some area codes and want to use the Left function.
What I want is simply to be able to identify the area code so that it may be
deleted, used in a make table query, etc. According to Special Edition Using
Access 2000, by Roger Jennings, page 335, the following should work:

Left ("phone",3)=999

However, when I try to use this in an Update query in the criteria row, the
following message is returned to me. The Expression you entered has a
function containing the wrong number of arguments.

Also, I have found that when I actually update a phone number with an area
code change and I use the following, I don't have any problems.

In the Update row: 777&right([phone],8)

the area code is updated to 777 just fine. 8 has to be used because most of
the phone numbers are formated with a dash between the exchange and last four
digits. Notice the ampersand after 777.

What I am also trying to do is isolate area codes in this way:

Left ("phone",3)=999,222,555. These are several area codes in one
metropolitan area.

What is the correct code to use so that I won't receive these syntax errors?

Then I can do a make table query of the isolated area codes or a delete
query or if necessary, an update query.