Update cell so only middle characters are selected

[Guest]

I have a series of text entries that are for securities descriptions. Each
field follows the general pattern of: text, "", text, "", text, "", text I
want to select, "", text, 4 times "", text. Now the text fields vary in
size, but the sequence of spaces does not. How can I pull the fourth text
field only out of the access cell?

Thanks for any advice

 

[Guest]

Hi Santa Monica,

If I understand you correctly, with data that looked like this:

text, "", text, "", text, "", Tom
text, "", text, "", text, "", Cindy

You would want to return:

Tom
Cindy

Is this correct? If so, try the following query. You will need to
substitute the actual name of your table in two places where "TableName" is
indicated, and the actual name of the field in four places, where "TextField"
is indicated. To use this query, create a new query in design view. Dismiss
the table dialog without adding any tables. Click on View > SQL View. You
should see the word "SELECT" highlighted. Copy the SQL (Structured Query
Language) statement shown below, and paste it into the SQL view, replacing
the original word "SELECT". Make the indicated changes to the table and field
names. You can then return to the more familiar query design view, by
clicking on View > Design View, or you can just run the query by clicking on
the maroon exclamation mark.

SELECT [TableName].[TextField],
Right$([TextField],Len([TextField])-InStrRev([TextField],",")-1)
AS FourthTextValue
FROM [TableName];


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Perhaps an even better SQL statement. This one will prevent #Error from
showing up in any fields that include a zero length string, or are null:

SELECT [TableName].[TextField],
Right$([TextField],Len([TextField])-InStrRev([TextField],",")-1)
AS FourthTextValue
FROM [TableName]
WHERE [TableName].[TextField] Is Not Null
And [TableName].[TextField]<>""


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Santa Monica,

If I understand you correctly, with data that looked like this:

text, "", text, "", text, "", Tom
text, "", text, "", text, "", Cindy

You would want to return:

Tom
Cindy

Is this correct? If so, try the following query. You will need to
substitute the actual name of your table in two places where "TableName" is
indicated, and the actual name of the field in four places, where "TextField"
is indicated. To use this query, create a new query in design view. Dismiss
the table dialog without adding any tables. Click on View > SQL View. You
should see the word "SELECT" highlighted. Copy the SQL (Structured Query
Language) statement shown below, and paste it into the SQL view, replacing
the original word "SELECT". Make the indicated changes to the table and field
names. You can then return to the more familiar query design view, by
clicking on View > Design View, or you can just run the query by clicking on
the maroon exclamation mark.

SELECT [TableName].[TextField],
Right$([TextField],Len([TextField])-InStrRev([TextField],",")-1)
AS FourthTextValue
FROM [TableName];


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html __________________________________________

I have a series of text entries that are for securities descriptions. Each
field follows the general pattern of: text, "", text, "", text, "", text I
want to select, "", text, 4 times "", text. Now the text fields vary in
size, but the sequence of spaces does not. How can I pull the fourth text
field only out of the access cell?

Thanks for any advice