stripping off characters after a comma

[bird lover]

I use access 2003 and access 2000 depending on workstation. The field
contains information typically as follows:
865 Main Street, Gardenia City, Hempstead (Town, Outside Of Villages), NY

In an update field, I want to place 865 Main Street in Field 13, using a
query on Field 10. I don't know programming. What is the proper syntax in
an update query for field 13.

Thanks for any help

 

[Douglas J. Steele]

Left([Field10], InStr([Field10], ",")

 

[bird lover]

I get the following error: The expression you entered is missing a closing
parenthesis,brack(]) or vertical bar. I copied and pasted Mr. Steele's
suggestion exactly into an update query.

Left([Field10], InStr([Field10], ",")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I use access 2003 and access 2000 depending on workstation. The field
contains information typically as follows:
865 Main Street, Gardenia City, Hempstead (Town, Outside Of Villages), NY

In an update field, I want to place 865 Main Street in Field 13, using a
query on Field 10. I don't know programming. What is the proper syntax
in
an update query for field 13.

Thanks for any help

 

[bird lover]

It worked. Thanks a lot.

Back up your table first because the change will be permanent.

Field: Field13
Table: YourTableName
Update To: Mid(Field10,1,(InStr(1,Field10,",",1)-1))

You really should give meaningful names to fields to avoid confusion.

Chris
Microsoft MVP

 

[Douglas J. Steele]

Actually, to exclude the comma, you need

Left([Field10], InStr([Field10], ",") - 1)

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Add one more closing parenthesis on the far right.

Chris
Microsoft MVP

I get the following error: The expression you entered is missing a
closing
parenthesis,brack(]) or vertical bar. I copied and pasted Mr. Steele's
suggestion exactly into an update query.