instr for select Query

[CW]

Hi.

I've rersearched this forum extensivley and get the idea on how to do
it but don't have enough experience to make it all the way.

In a MS Access select query, I have a field that returns names in the
following format:

;#Lastname1, Firstname1;#Lastname2, Firstname2;#

I can't seem to sting the instr, len etc, functions together properly
to clean up the string to:

Lastname1, Firstname1; Lastname2, Firstname2

I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:

Lastname2, Firstname2;#

Any help would greatly be appreciated.

 

[BruceM]

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you are
using in the calculated field.

 

[CW]

Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
                 [Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to guess.
Are LastName1 etc. fields?  How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL.  To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you are
using in the calculated field.

Hi.

I've rersearched this forum extensivley and get the idea on how to do
it but don't have enough experience to make it all the way.

In a MS Access select query, I have a field that returns names in the
following format:

;#Lastname1, Firstname1;#Lastname2, Firstname2;#

I can't seem to sting the instr, len etc, functions together properly
to clean up the string to:

Lastname1, Firstname1; Lastname2, Firstname2

I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:

Lastname2, Firstname2;#

Any help would greatly be appreciated.- Hide quoted text -

- Show quoted text -

 

[BruceM]

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")

However, I expect that LastName1 etc. are variables, or the text string is a
field, so I don't know how this would relate to your project.

Again, if you post the SQL or the expression there would be a chance of
providing a specific response.

Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you
are
using in the calculated field.

Hi.

I've rersearched this forum extensivley and get the idea on how to do
it but don't have enough experience to make it all the way.

In a MS Access select query, I have a field that returns names in the
following format:

;#Lastname1, Firstname1;#Lastname2, Firstname2;#

I can't seem to sting the instr, len etc, functions together properly
to clean up the string to:

Lastname1, Firstname1; Lastname2, Firstname2

I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:

Lastname2, Firstname2;#

Any help would greatly be appreciated.- Hide quoted text -

- Show quoted text -

 

[CW]

No sorry for the confusion, they're not accessible fields Lastname,
Firstname, just one field called "Investigators" with the returned
string as shown.

Investigator
***************************************
;#Baxter, Bill;#Greztky, Wayne;#
;#Soren, Lilly;#Naomi, Norm;#
;#Wilson, Sam;#Doe, John;#

and I need to parse each line using a select query to:

Investigator
***************************************
Baxter, Bill; Greztky, Wayne
Soren, Lilly; Naomi, Norm
Wilson, Sam; Doe, John
(note here that I've added a space between the two names)

That should clear it up

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")

However, I expect that LastName1 etc. are variables, or the text string isa
field, so I don't know how this would relate to your project.

Again, if you post the SQL or the expression there would be a chance of
providing a specific response.


Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you
are
using in the calculated field.

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[BruceM]

Substitute the field name for the text string:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],3))-2),";#",", ")

Now that I look at it again, you should be able to simplify it a bit:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],5))),";#",", ")

See Help for more information about the functions. Use sections of the
expression, such as:
MidTest: Mid([Investigator],3
or
LenTest: Len(Mid([Investigator],5))
so that you can see how the functions parse the text.

When using the newsgroups in the future, read the responses carefully before
responding. If somebody asks that you provide additional information such
as code or an expression, go ahead and do it unless there is a specific
reason why not.


No sorry for the confusion, they're not accessible fields Lastname,
Firstname, just one field called "Investigators" with the returned
string as shown.

Investigator
***************************************
;#Baxter, Bill;#Greztky, Wayne;#
;#Soren, Lilly;#Naomi, Norm;#
;#Wilson, Sam;#Doe, John;#

and I need to parse each line using a select query to:

Investigator
***************************************
Baxter, Bill; Greztky, Wayne
Soren, Lilly; Naomi, Norm
Wilson, Sam; Doe, John
(note here that I've added a space between the two names)

That should clear it up

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")

However, I expect that LastName1 etc. are variables, or the text string is
a
field, so I don't know how this would relate to your project.

Again, if you post the SQL or the expression there would be a chance of
providing a specific response.


Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to
guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you
are
using in the calculated field.

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[CW]

Thanks. Can you explain how the value '5' figures in this? 3 means to
start from the third character in from the left right?

Substitute the field name for the text string:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],3))-2),";#",", ")

Now that I look at it again, you should be able to simplify it a bit:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],5))),";#",", ")

See Help for more information about the functions.  Use sections of the
expression, such as:
MidTest: Mid([Investigator],3
or
LenTest: Len(Mid([Investigator],5))
so that you can see how the functions parse the text.

When using the newsgroups in the future, read the responses carefully before
responding.  If somebody asks that you provide additional information such
as code or an expression, go ahead and do it unless there is a specific
reason why not.



No sorry for the confusion, they're not accessible fields Lastname,
Firstname, just one field called "Investigators" with the returned
string as shown.

Investigator
***************************************
;#Baxter, Bill;#Greztky, Wayne;#
;#Soren, Lilly;#Naomi, Norm;#
;#Wilson, Sam;#Doe, John;#

and I need to parse each line using a select query to:

Investigator
***************************************
Baxter, Bill; Greztky, Wayne
Soren, Lilly; Naomi, Norm
Wilson, Sam; Doe, John
(note here that I've added a space between the two names)

That should clear it up

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")

However, I expect that LastName1 etc. are variables, or the text string is
a
field, so I don't know how this would relate to your project.

Again, if you post the SQL or the expression there would be a chance of
providing a specific response.

"CW" <[email protected]> wrote in message

Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to
guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression you
are
using in the calculated field.

Hi.
I've rersearched this forum extensivley and get the idea on how to do
it but don't have enough experience to make it all the way.
In a MS Access select query, I have a field that returns names in the
following format:
;#Lastname1, Firstname1;#Lastname2, Firstname2;#
I can't seem to sting the instr, len etc, functions together properly
to clean up the string to:
Lastname1, Firstname1; Lastname2, Firstname2
I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:
Lastname2, Firstname2;#
Any help would greatly be appreciated.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[BruceM]

Mid([Investigator],3) is a text string equal to the Investigator field
starting from the third character. If Investigator is 32 characters in
length, the Len function returns 30:
Len(Mid([Investigator],3))
Subtract 2 from that and you get 28. If Investigator contains:
;#Baxter, Bill;#Greztky, Wayne;#
then
Baxter, Bill;#Greztky, Wayne returns:
Baxter, Bill;#Greztky, Wayne;#
Len for this returns 30. Subtract 2 from that to get 28.
Left("Baxter, Bill;#Greztky, Wayne;#",28)
returns
Baxter, Bill;#Greztky, Wayne

Mid([Investigator],5) returns:
xter, Bill;#Greztky, Wayne;#
which is 28 characters in length. Rather than using 30 and subtracting 2 I
contrived to return a shorter string.

Replace just replaces one bit of text with another.

I took a shortcut to return a Len number equal to what I knew was needed.
If the format is always the same, this will work, although the slightly
longer expression with the -2 is easier to understand.

There are probably other ways to do this. Some of them may be more
efficient than what I came up with.

Thanks. Can you explain how the value '5' figures in this? 3 means to
start from the third character in from the left right?

Substitute the field name for the text string:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],3))-2),";#",",
")

Now that I look at it again, you should be able to simplify it a bit:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],5))),";#",", ")

See Help for more information about the functions. Use sections of the
expression, such as:
MidTest: Mid([Investigator],3
or
LenTest: Len(Mid([Investigator],5))
so that you can see how the functions parse the text.

When using the newsgroups in the future, read the responses carefully
before
responding. If somebody asks that you provide additional information such
as code or an expression, go ahead and do it unless there is a specific
reason why not.



No sorry for the confusion, they're not accessible fields Lastname,
Firstname, just one field called "Investigators" with the returned
string as shown.

Investigator
***************************************
;#Baxter, Bill;#Greztky, Wayne;#
;#Soren, Lilly;#Naomi, Norm;#
;#Wilson, Sam;#Doe, John;#

and I need to parse each line using a select query to:

Investigator
***************************************
Baxter, Bill; Greztky, Wayne
Soren, Lilly; Naomi, Norm
Wilson, Sam; Doe, John
(note here that I've added a space between the two names)

That should clear it up

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")

However, I expect that LastName1 etc. are variables, or the text string
is
a
field, so I don't know how this would relate to your project.

Again, if you post the SQL or the expression there would be a chance of
providing a specific response.

"CW" <[email protected]> wrote in message

Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.

Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to
guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in
design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression
you
are
using in the calculated field.

Hi.
I've rersearched this forum extensivley and get the idea on how to
do
it but don't have enough experience to make it all the way.
In a MS Access select query, I have a field that returns names in
the
following format:
;#Lastname1, Firstname1;#Lastname2, Firstname2;#
I can't seem to sting the instr, len etc, functions together
properly
to clean up the string to:
Lastname1, Firstname1; Lastname2, Firstname2
I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:
Lastname2, Firstname2;#
Any help would greatly be appreciated.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[CW]

Hmm thanks again, I'll give it a shot. I've used functions in Access
for years and have never seen replace.

Mid([Investigator],3) is a text string equal to the Investigator field
starting from the third character.  If Investigator is 32 characters in
length, the Len function returns 30:
Len(Mid([Investigator],3))
Subtract 2 from that and you get 28.  If Investigator contains:
;#Baxter, Bill;#Greztky, Wayne;#
then
Baxter, Bill;#Greztky, Wayne returns:
Baxter, Bill;#Greztky, Wayne;#
Len for this returns 30.  Subtract 2 from that to get 28.
Left("Baxter, Bill;#Greztky, Wayne;#",28)
returns
Baxter, Bill;#Greztky, Wayne

Mid([Investigator],5) returns:
xter, Bill;#Greztky, Wayne;#
which is 28 characters in length.  Rather than using 30 and subtracting 2 I
contrived to return a shorter string.

Replace just replaces one bit of text with another.

I took a shortcut to return a Len number equal to what I knew was needed.
If the format is always the same, this will work, although the slightly
longer expression with the -2 is easier to understand.

There are probably other ways to do this.  Some of them may be more
efficient than what I came up with.


Thanks. Can you explain how the value '5' figures in this? 3 means to
start from the third character in from the left right?

Substitute the field name for the text string:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],3))-2),";#",",
")

Now that I look at it again, you should be able to simplify it a bit:
NewField:
Replace(Left(Mid([Investigator],3),Len(Mid([Investigator],5))),";#",", ")

See Help for more information about the functions. Use sections of the
expression, such as:
MidTest: Mid([Investigator],3
or
LenTest: Len(Mid([Investigator],5))
so that you can see how the functions parse the text.

When using the newsgroups in the future, read the responses carefully
before
responding. If somebody asks that you provide additional information such
as code or an expression, go ahead and do it unless there is a specific
reason why not.

No sorry for the confusion, they're not accessible fields Lastname,
Firstname, just one field called "Investigators" with the returned
string as shown.

Investigator
***************************************
;#Baxter, Bill;#Greztky, Wayne;#
;#Soren, Lilly;#Naomi, Norm;#
;#Wilson, Sam;#Doe, John;#

and I need to parse each line using a select query to:

Investigator
***************************************
Baxter, Bill; Greztky, Wayne
Soren, Lilly; Naomi, Norm
Wilson, Sam; Doe, John
(note here that I've added a space between the two names)

That should clear it up

Using the literal values you provided you could do something like:
NewField: Replace(Left(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3),len(Mid(";#Lastname1,
Firstname1;#Lastname2,Firstname2;#",3))-2),";#",", ")
However, I expect that LastName1 etc. are variables, or the text string
is
a
field, so I don't know how this would relate to your project.
Again, if you post the SQL or the expression there would be a chance of
providing a specific response.
Oh yes that's right. No this string returned is from a SharePoint
list, so I only have ";#Lastname1, Firstname1;#Lastname2,
Firstname2;#" to work with.
Maybe you would use something like:
NewField: [Lastname1] & ", " & [Firstname1] & "; " & _
[Lastname2] & ", " & [Firstname2]
However, since you haven't given any details it is only possible to
guess.
Are LastName1 etc. fields? How are you using the functions.
One way to provide the information that would help somebody suggest an
answer is to post the query's SQL. To do that, open the query in
design
view, click View > SQL, copy the code, and post it here.
Another way to provide the information may be to post the expression
you
are
using in the calculated field.

Hi.
I've rersearched this forum extensivley and get the idea on how to
do
it but don't have enough experience to make it all the way.
In a MS Access select query, I have a field that returns names in
the
following format:
;#Lastname1, Firstname1;#Lastname2, Firstname2;#
I can't seem to sting the instr, len etc, functions together
properly
to clean up the string to:
Lastname1, Firstname1; Lastname2, Firstname2
I've seen the Microsoft examples as well, I think I have to parse
through it to break it up then concatenate back together the cleaned
names, but the closest I can get is:
Lastname2, Firstname2;#
Any help would greatly be appreciated.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -