Still getting the same #Error
:
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
..
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
:
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
..
Still getting the #Error in the field
:
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"
:
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
:
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
:
Do I place this in the criteria line when making the query?
:
Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1)
--
KARL DEWEY
Build a little - Test a little
:
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.
