ok here is my sql statement
SELECT dbo_comps11.title, dbo_comps11.addr, dbo_comps11.city,
dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.path,
Len([path])-Len(Replace([path],"/","")) AS SegmentCount,
SplitPath([path],1)
AS PartOne, SplitPath([path],2) AS PartTwo, SplitPath([path],3) AS
PartThree,
Splitpath([path],4) AS PartFour
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_comps11.state)="CT") AND
((Len([path])-Len(Replace([path],"/","")))=5));
i did not do anything with the function statement because I am not sure
what
to do with it
:
DId you copy the function from Stefan Hoffman's post?
Did you paste the function into a module and save the module with a
name
that is not the same as the function?
IF not, then you need to do that.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
OK, when I insert Len([Field]) - Len(Replace([Field], "/","")) into
the
fielde and set the criteria to 5 the insert SplitPath([Field],1)
SplitPath([Field],2) SplitPath([Field],3) SplitPath([Field],4) into
each
consecutive field in the query then change the word [field] to the
name
of
the original field or the name of the table i get a message
"undefined
function SplitPath in expression.
:
Field: SegmentCount: Len([Field]) - Len(Replace([Field], "/",""))
Criteria: 5
Field: PartOne: SplitPath([Field],1)
Field: PartTwo: SplitPath([Field],2)
etc.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
message
Hello, I placed the Len([Field]) - Len(Replace([Field], "/","")
= 5
in the criteria for thr Path Field and received an erroe message
"The
expression you entered is missing a closing parenthesis, bracket
(]),
or
vertical bar(|)
I am a novice and may require specific instructions. Also how
and
where
do
I use the funtion
:
hi,
Finger Tips wrote:
I have the following field titled Path as part of a full
query,
that
has the
following format of data, each row in the same format but
different
text.
/Community & Government/Social & Human Services/Individual &
Family/Abuse/
Other rows in this field contain just
/Community & Government/Social & Human Services/
I am looking to run the query so it returns only listings that
contain
the
four /../../../../ and then seperate the four into seperate
fields.
Use
Len([Field]) - Len(Replace([Field], "/","") = 5
as criteria to select your path.
Use a function like
Public Function SplitPath(AString As String, AItem) As String
Dim a() As String
a() = Split(AString, "/")
SplitPath = a(AItem)
End Function
to extract your path items.
mfG
--> stefan <--
