Textpart

[colin]

Many thanks to Ken Snell [MVP] and to Douglas Steele for the help with my
earlier post yesterday.

My question was as follows:

Hi I have an access database that has a text field with example data as
The answer Provided by Ken was

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

I have tried using the following:

SELECT Mid([Gamma 0800RatedCDR]![Location], InStr([Gamma
0800RatedCDR]![Location] , " - ") + 3) as Affiliation From [Gamma
0800RatedCDR];

However I get the following error

The Syntax of the Subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parenthesis

Please can anyone advise.

Many thanks in anticipation

Kind rgds

Colin

 

[Ken Snell [MVP]]

Change the ! to . in the query.

Other than that, the query that you posted looks ok. How are you trying to
run this query? By opening the query manually from database window? From VBA
code? other?

--

Ken Snell
<MS ACCESS MVP>

Many thanks to Ken Snell [MVP] and to Douglas Steele for the help with my
earlier post yesterday.

My question was as follows:

Hi I have an access database that has a text field with example data as
The answer Provided by Ken was

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

I have tried using the following:

SELECT Mid([Gamma 0800RatedCDR]![Location], InStr([Gamma
0800RatedCDR]![Location] , " - ") + 3) as Affiliation From [Gamma
0800RatedCDR];

However I get the following error

The Syntax of the Subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parenthesis

Please can anyone advise.

Many thanks in anticipation

Kind rgds

Colin

 

[colin]

Hi Ken Many thanks it works!!

It was purley a select quer to tidy up a report without changing the
database structure.

Anyhow your continued help in this newsgroup is much appreciated

Change the ! to . in the query.

Other than that, the query that you posted looks ok. How are you trying to
run this query? By opening the query manually from database window? From VBA
code? other?

--

Ken Snell
<MS ACCESS MVP>

Many thanks to Ken Snell [MVP] and to Douglas Steele for the help with my
earlier post yesterday.

My question was as follows:

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

The answer Provided by Ken was

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

I have tried using the following:

SELECT Mid([Gamma 0800RatedCDR]![Location], InStr([Gamma
0800RatedCDR]![Location] , " - ") + 3) as Affiliation From [Gamma
0800RatedCDR];

However I get the following error

The Syntax of the Subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parenthesis

Please can anyone advise.

Many thanks in anticipation

Kind rgds

Colin

 

[Ken Snell [MVP]]

Hi Ken Many thanks it works!!

Glad to hear this.

Anyhow your continued help in this newsgroup is much appreciated

You're welcome.