Textpart

[Guest]

Hi I have an access database that has a text field with example data as
follows:

john smith - British airways
johnathen Smith - British Airways
Judy - National Geographic

Is there a way, using a Select query to return just the data after the "-"

Please accept my thanks in anticipation.

Kind Rgds
Colin

 

[Ken Snell [MVP]]

SELECT Mid([FieldName], InStr([FieldName], " - ") + 3) AS Affiliation
FROM TableName;

 

[Douglas J. Steele]

Assuming there's only a single space between the hyphen and the text, I
think that needs to be

SELECT Mid([FieldName], InStr([FieldName], " - ") + 2) AS Affiliation FROM
TableName;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT Mid([FieldName], InStr([FieldName], " - ") + 3) AS Affiliation
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>

Hi I have an access database that has a text field with example data as
follows:

john smith - British airways
johnathen Smith - British Airways
Judy - National Geographic

Is there a way, using a Select query to return just the data after the
"-"

Please accept my thanks in anticipation.

Kind Rgds
Colin

 

[Ken Snell [MVP]]

Would depend upon whether you want to include the space after the hyphen in
the extracted string or not. If the text string is
ken - snell

Using +3, the expression would return
snell

Using +2, the expression would return (note the leading space character)
snell

--

Ken Snell
<MS ACCESS MVP>

Assuming there's only a single space between the hyphen and the text, I
think that needs to be

SELECT Mid([FieldName], InStr([FieldName], " - ") + 2) AS Affiliation FROM
TableName;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT Mid([FieldName], InStr([FieldName], " - ") + 3) AS Affiliation
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>

Hi I have an access database that has a text field with example data as
follows:

john smith - British airways
johnathen Smith - British Airways
Judy - National Geographic

Is there a way, using a Select query to return just the data after the
"-"

Please accept my thanks in anticipation.

Kind Rgds
Colin

 

[Douglas J. Steele]

Actually, the problem was that I didn't notice that you were looking for " -
", not "-".

Sorry for the intrusion...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Would depend upon whether you want to include the space after the hyphen
in the extracted string or not. If the text string is
ken - snell

Using +3, the expression would return
snell

Using +2, the expression would return (note the leading space character)
snell

--

Ken Snell
<MS ACCESS MVP>

Assuming there's only a single space between the hyphen and the text, I
think that needs to be

SELECT Mid([FieldName], InStr([FieldName], " - ") + 2) AS Affiliation
FROM TableName;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT Mid([FieldName], InStr([FieldName], " - ") + 3) AS Affiliation
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>



Hi I have an access database that has a text field with example data as
follows:

john smith - British airways
johnathen Smith - British Airways
Judy - National Geographic

Is there a way, using a Select query to return just the data after the
"-"

Please accept my thanks in anticipation.

Kind Rgds
Colin

 

[Ken Snell [MVP]]

No problem!

--

Ken Snell
<MS ACCESS MVP>

Actually, the problem was that I didn't notice that you were looking for
" - ", not "-".

Sorry for the intrusion...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Would depend upon whether you want to include the space after the hyphen
in the extracted string or not. If the text string is
ken - snell

Using +3, the expression would return
snell

Using +2, the expression would return (note the leading space character)
snell

--

Ken Snell
<MS ACCESS MVP>

Assuming there's only a single space between the hyphen and the text, I
think that needs to be

SELECT Mid([FieldName], InStr([FieldName], " - ") + 2) AS Affiliation
FROM TableName;

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SELECT Mid([FieldName], InStr([FieldName], " - ") + 3) AS Affiliation
FROM TableName;

--

Ken Snell
<MS ACCESS MVP>



Hi I have an access database that has a text field with example data
as
follows:

john smith - British airways
johnathen Smith - British Airways
Judy - National Geographic

Is there a way, using a Select query to return just the data after the
"-"

Please accept my thanks in anticipation.

Kind Rgds
Colin