Display certain words from a sentence

[Nishikawa]

Hi,

I would like to know if it is possible to display a certain word within a sentence separated by commas. Eg: New York, new york, United States

I would like MS Access to show first words (New York) in one column and last words (United States) in another column. I am unable to use left or right function as the length of the words and the number of commas are unknown is very row.


Thanks

 

[ajetrumpet]

you need code similar to that of what you can find here:

http://www.access-programmers.co.uk/forums/showthread.php?p=705161#post705161

Instr() is your function for something like this. You will find that it is the solution for varying positions of a certain type of character, like a comma.

 

[WayneRyan]

Nishi,

You're gonna have to use a Public Function for this:

Your query:

Select fnGetWords([YourField], "First") As FirstWord,
       fnGetWords([YourField], "Last")  As LastWord,
From   YourTable

In a Public module:

Public Function fnGetWords(TheString As String, WhichElement As String) As String

dim varArray as Variant
varArray = Split(TheString, ",")

If WhichElement = "First" Then
   fnGetWords = varArray(0)
Else
   fnGetWords = varArray(UBound(varArray))
End If

End Function

Wayne

 

[Nishikawa]

Thanks man!! May I know how do you know all these functions? I would like to read more about it so that I do not have to keep bothering you guys for such simple problems.

 

[boblarson]

Thanks man!! May I know how do you know all these functions? I would like to read more about it so that I do not have to keep bothering you guys for such simple problems.

It's called "experience" and also knowing how to search.

1. Use the VBA help file. It actually has a lot of great material there.

2. Use the search facility on this website. It really does work.

3. Use Google. Start by using the proper starting keywords like:

Microsoft Access VBA Whatever function

and you will see more than you've ever thought possible.

 

[ajetrumpet]

and you will see more than you've ever thought possible.

aint that the truth.

 

[doco]

Bob was right! Google search on

Microsoft Access VBA Whatever function

Returned 18,300 responses

 

[boblarson]

And I hope that the meaning of "Whatever Function" came through in that you substitute the name of the function you are interested in.

 

[doco]

Indeed, just having a little fun...

 

[Mike375]

Actually I think forums like this often give a more complete answer than you get from Google or Access Help.

Left() and Right() are a good example. From the forum you are far more likely to get the other vital parts such as InStr, Len and Trim.

I have often thought that a good addition to a forum would be a box/field where one of the posters on a thread could make an entry so as to make "search" much better.

As a side note it is quite interesting to check Who's On Line and look at the threads being viewed by Guest. Most of them way back in time.

 

[boblarson]

Actually, you can find posts on the forums really quick and easy by using Google.

 

[Mike375]

Actually, you can find posts on the forums really quick and easy by using Google.

I seem to remember Google was an option for Search on the forum but can't see it now......Friday afternoon

Bob, as an example what would you enter to search for something like this topic. Right or Left would get plenty if the search was on content of posting but a lot would be irrelevant.

 

[gemma-the-husky]

actually, i thought waynes snippet was really impressive

i have tended to do stuff like this the hard way, reading strings a char at a time. it works, but ill try to remember waynes idea in the future ...

------------
now a similar example

if you have a string that you want to process a character at a time - say to add the ascii values of each char, or to change certain characters - is there an easy way of doing this

i am doing it by string slicing, a character at a time with mid function, and then reassembling the string, but it is very awkward in that mid(mystring,1) returns a string, which cant be handled in quite the same way as a char or byte (or can it?)

 

[ajetrumpet]

Bob was right! Google search on


Returned 18,300 responses

how how many of those responses are relevent?? 3?