seperate fields

[Guest]

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.

 

[Stefan Hoffmann]

hi,

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 <--

 

[Guest]

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,

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 <--

 

[John Spencer]

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
..

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,

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 <--

 

[Guest]

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
..

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 <--

 

[John Spencer]

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
..

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 <--

 

[Guest]

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
..

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 <--

 

[John Spencer]

Well, you have to copy the function and paste it into a VBA module. If you
don't then nothing is going to happen other than you getting the Undefined
function error. By the way the assumption that Stefan (and I) made is that
you are using Access 2000 or later.

Does this query return the records you want to work on? If so, then the
next step is to split the path into its component parts. The easiest way to
do that is to use Stefan's proposed VBA function.

SELECT dbo_comps11.title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.path
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));

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

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
..

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 <--

 

[Guest]

Hello John, I still get an invalid procedure call when running this.

Well, you have to copy the function and paste it into a VBA module. If you
don't then nothing is going to happen other than you getting the Undefined
function error. By the way the assumption that Stefan (and I) made is that
you are using Access 2000 or later.

Does this query return the records you want to work on? If so, then the
next step is to split the path into its component parts. The easiest way to
do that is to use Stefan's proposed VBA function.

SELECT dbo_comps11.title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.path
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));

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

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
..

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 <--

 

[John Spencer]

If path is ever null then you could run into a problem. Does the following
work?

SELECT dbo_comps11.title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.path
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(Nz([path],""))-Len(Replace(NZ([path],""),"/",""))=5

If so, then we can try adding in the function to get sections of the path.

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

Hello John, I still get an invalid procedure call when running this.

Well, you have to copy the function and paste it into a VBA module. If
you
don't then nothing is going to happen other than you getting the
Undefined
function error. By the way the assumption that Stefan (and I) made is
that
you are using Access 2000 or later.

Does this query return the records you want to work on? If so, then the
next step is to split the path into its component parts. The easiest way
to
do that is to use Stefan's proposed VBA function.

SELECT dbo_comps11.title
, dbo_comps11.addr
, dbo_comps11.city
, dbo_comps11.state
, dbo_comps11.zip
, dbo_comps11.phone
, dbo_paths.path
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));

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

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 <--