Trim Data

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are blank.
WHen I insert this statement though I get an "The expression you entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since Left
will not accept a negative number. It will accept zero and positive
numbers.

So the trick is to add the comma to the end of the field value in the
instr function call. That way the function will always return 1 when the
field is null or blank or doesn't contain a comma other than then one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid add this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is the street
address without the city state or zip. So I guess something that will trim
everything after the first comma knowing that sometime the field is blank.

Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are blank.
WHen I insert this statement though I get an "The expression you entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since Left
will not accept a negative number. It will accept zero and positive
numbers.

So the trick is to add the comma to the end of the field value in the
instr function call. That way the function will always return 1 when the
field is null or blank or doesn't contain a comma other than then one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid add this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is the street
address without the city state or zip. So I guess something that will trim
everything after the first comma knowing that sometime the field is blank.

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news
Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since Left
will not accept a negative number. It will accept zero and positive
numbers.

So the trick is to add the comma to the end of the field value in the
instr function call. That way the function will always return 1 when
the
field is null or blank or doesn't contain a comma other than then one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is the
street
address without the city state or zip. So I guess something that
will trim
everything after the first comma knowing that sometime the field
is blank.

here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news
Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since Left
will not accept a negative number. It will accept zero and positive
numbers.

So the trick is to add the comma to the end of the field value in the
instr function call. That way the function will always return 1 when
the
field is null or blank or doesn't contain a comma other than then one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is the
street
address without the city state or zip. So I guess something that
will trim
everything after the first comma knowing that sometime the field
is blank.

BAD brackets. You should surround the tablename and fieldname separately as
follows.

Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street

SELECT dbo_comps11.title AS dbo_comps11_title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.id, dbo_paths.title AS dbo_paths_title
, dbo_paths.parent
, Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

If your field name is unique with the two tables you could use

Left([addr],InStr(1,[addr] & ",",",")-1) AS Street

And if it is unique and has no spaces or other non letter or number
characters you can even drop the brackets

Left(addr,InStr(1,addr & ",",",")-1) AS Street

Hope that takes care of the problem.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
...
here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in
a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news
Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since
Left
will not accept a negative number. It will accept zero and
positive
numbers.

So the trick is to add the comma to the end of the field value in
the
instr function call. That way the function will always return 1
when
the
field is null or blank or doesn't contain a comma other than then
one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid
add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is
the
street
address without the city state or zip. So I guess something
that
will trim
everything after the first comma knowing that sometime the
field
is blank.

Still getting the same #Error

"John Spencer" wrote:


BAD brackets. You should surround the tablename and fieldname separately as
follows.

Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street

SELECT dbo_comps11.title AS dbo_comps11_title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.id, dbo_paths.title AS dbo_paths_title
, dbo_paths.parent
, Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

If your field name is unique with the two tables you could use

Left([addr],InStr(1,[addr] & ",",",")-1) AS Street

And if it is unique and has no spaces or other non letter or number
characters you can even drop the brackets

Left(addr,InStr(1,addr & ",",",")-1) AS Street

Hope that takes care of the problem.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
...
here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in
a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since
Left
will not accept a negative number. It will accept zero and
positive
numbers.

So the trick is to add the comma to the end of the field value in
the
instr function call. That way the function will always return 1
when
the
field is null or blank or doesn't contain a comma other than then
one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid
add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is
the
street
address without the city state or zip. So I guess something
that
will trim
everything after the first comma knowing that sometime the
field
is blank.

Ok John, I found some other items in the field that may be effecting the
result. Here are some of the data as it appears in the addr field. In these
cases I would just want the field returned blank.

# 99.5, Key West, FL 33040
(401)-996-1670, Riverside, RI 02915
.., Bridgeton, NJ 08302
.., Huntingdon Valley, PA 19006
.., Jacksonville, FL 32202
.., Lynnfield, MA 01940
.., Phoenixville, PA 19460
.., Tampa, FL 33612
.., Tampa, FL 33612
.., Tampa, FL 33612
.., Williamsport, PA 17702
...., Anderson, SC 29621
...., Greenville, SC 29601
/NP/, Redmond, WA 98053
?, Colchester, VT 05446
_, Boston, MA 02284


"Finger Tips" wrote:

Still getting the same #Error

"John Spencer" wrote:


BAD brackets. You should surround the tablename and fieldname separately as
follows.

Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street

SELECT dbo_comps11.title AS dbo_comps11_title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.id, dbo_paths.title AS dbo_paths_title
, dbo_paths.parent
, Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

If your field name is unique with the two tables you could use

Left([addr],InStr(1,[addr] & ",",",")-1) AS Street

And if it is unique and has no spaces or other non letter or number
characters you can even drop the brackets

Left(addr,InStr(1,addr & ",",",")-1) AS Street

Hope that takes care of the problem.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
...
here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in
a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since
Left
will not accept a negative number. It will accept zero and
positive
numbers.

So the trick is to add the comma to the end of the field value in
the
instr function call. That way the function will always return 1
when
the
field is null or blank or doesn't contain a comma other than then
one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid
add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is
the
street
address without the city state or zip. So I guess something
that
will trim
everything after the first comma knowing that sometime the
field
is blank.

At this point it is time to apply someone's brain and clean up the data.
This is getting pretty complicated.

While I could develop a VBA function that would clean out some of this
data, it would be more time than I am willing to donate.

Sorry.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Ok John, I found some other items in the field that may be effecting the
result. Here are some of the data as it appears in the addr field. In these
cases I would just want the field returned blank.

# 99.5, Key West, FL 33040
(401)-996-1670, Riverside, RI 02915
., Bridgeton, NJ 08302
., Huntingdon Valley, PA 19006
., Jacksonville, FL 32202
., Lynnfield, MA 01940
., Phoenixville, PA 19460
., Tampa, FL 33612
., Tampa, FL 33612
., Tampa, FL 33612
., Williamsport, PA 17702
..., Anderson, SC 29621
..., Greenville, SC 29601
/NP/, Redmond, WA 98053
?, Colchester, VT 05446
_, Boston, MA 02284


"Finger Tips" wrote:

Still getting the same #Error

"John Spencer" wrote:

BAD brackets. You should surround the tablename and fieldname separately as
follows.

Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street

SELECT dbo_comps11.title AS dbo_comps11_title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.id, dbo_paths.title AS dbo_paths_title
, dbo_paths.parent
, Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

If your field name is unique with the two tables you could use

Left([addr],InStr(1,[addr] & ",",",")-1) AS Street

And if it is unique and has no spaces or other non letter or number
characters you can even drop the brackets

Left(addr,InStr(1,addr & ",",",")-1) AS Street

Hope that takes care of the problem.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
...
here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in
a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since
Left
will not accept a negative number. It will accept zero and
positive
numbers.

So the trick is to add the comma to the end of the field value in
the
instr function call. That way the function will always return 1
when
the
field is null or blank or doesn't contain a comma other than then
one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid
add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is
the
street
address without the city state or zip. So I guess something
that
will trim
everything after the first comma knowing that sometime the
field
is blank.

John, so there is no trim expression that will just leave everything to the
left of the first , no matter what is and eliminate everything to the right?

"John Spencer" wrote:

At this point it is time to apply someone's brain and clean up the data.
This is getting pretty complicated.

While I could develop a VBA function that would clean out some of this
data, it would be more time than I am willing to donate.

Sorry.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Ok John, I found some other items in the field that may be effecting the
result. Here are some of the data as it appears in the addr field. In these
cases I would just want the field returned blank.

# 99.5, Key West, FL 33040
(401)-996-1670, Riverside, RI 02915
., Bridgeton, NJ 08302
., Huntingdon Valley, PA 19006
., Jacksonville, FL 32202
., Lynnfield, MA 01940
., Phoenixville, PA 19460
., Tampa, FL 33612
., Tampa, FL 33612
., Tampa, FL 33612
., Williamsport, PA 17702
..., Anderson, SC 29621
..., Greenville, SC 29601
/NP/, Redmond, WA 98053
?, Colchester, VT 05446
_, Boston, MA 02284


"Finger Tips" wrote:

Still getting the same #Error

"John Spencer" wrote:

BAD brackets. You should surround the tablename and fieldname separately as
follows.

Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street

SELECT dbo_comps11.title AS dbo_comps11_title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.id, dbo_paths.title AS dbo_paths_title
, dbo_paths.parent
, Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS
Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

If your field name is unique with the two tables you could use

Left([addr],InStr(1,[addr] & ",",",")-1) AS Street

And if it is unique and has no spaces or other non letter or number
characters you can even drop the brackets

Left(addr,InStr(1,addr & ",",",")-1) AS Street

Hope that takes care of the problem.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
...
here is the sql

SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr,
dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone,
dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent,
Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street
FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON
dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent =
dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent
WHERE (((dbo_paths.parent)=60003));

various scenerios of the data in the orig. field are

1400 N. Scott, Wichita Falls, TX 76306
500 East Peabody Drive, Champaign, IL 61820
Hortonville, WI 54944
3448 Steinway Street, Long Island City, NY 11101
707 Lake Boulevard, Saint Joseph, MI 49085
and Blank

The query does run and returns all other fields correctly except this one
which returns the text #Error

"John Spencer" wrote:

I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)
and I don't get any error.

WHERE do you see the error? In the query itself or are you using this in
a
report or form?

If in the query, please post the SQL of the query.
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Finger Tips" wrote in message
news Still getting the #Error in the field

"John Spencer" wrote:

Typo on my part. Period should be a comma


LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1)

Extra spaces added for clarity

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Hi John, yes some of the fields only contain 2 commas and others are
blank.
WHen I insert this statement though I get an "The expression you
entered
contains an Invalid Syntax"

"John Spencer" wrote:

If your field does not have a comma in it that will error, since
Left
will not accept a negative number. It will accept zero and
positive
numbers.

So the trick is to add the comma to the end of the field value in
the
instr function call. That way the function will always return 1
when
the
field is null or blank or doesn't contain a comma other than then
one
you have temporarily added.


LEFT([YourField], Instr(1.[YourField] & ",",",")-1)



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Finger Tips wrote:
Received an "Error #" in the field when it ran the query

"KARL DEWEY" wrote:

Open your query in design view and in the Field row of the grid
add
this in a
blank.
Street: Left([YourFieldName], InStr([YourFieldName], ",")-1)

--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

Do I place this in the criteria line when making the query?

"KARL DEWEY" wrote:

Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little


"Finger Tips" wrote:

I know this may be answered somewhere but I am new at this and
need very
specific steps. Screen shots also help.

I am running a query. In one of the fields "addr: it has
information like
this example.

236 West Rezanof Drive, Kodiak, AK 99615
or
Kodiak, AK 99615

or

Some may be blank. The only thing I want from this field is
the
street
address without the city state or zip. So I guess something
that
will trim
everything after the first comma knowing that sometime the
field
is blank.