Instr Function in select query

[thenoisydrum]

Bonjour,
I need to select the second word from a product description.
Here are two examples;

2156015 Dunlop SP-30
1756514 Goodyear Sport

I found the correct code on another website to do this in Excel;

=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))

It works a treat.
In order for it to work in Access you apparently change FIND to Instr.
Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.
Here is what I am using. Description is obviously the field I am selecting from;

TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))

Where am I going wrong?

 

[spikepl]

Access string functions are here: http://www.techonthenet.com/access/functions/

Look up InStr

And when something built up of several bits does not do what you want, then build and test each bit before putting it all together.

 

[thenoisydrum]

But I'd appreciate some help with my particular example?
Come on guys, I know you want to help.......

 

[Brianwarnock]

Your instr is incorrect, which I guess is why spikepl suggested that you look it up as you will learn more thoroughly that way.

Brian

 

[thenoisydrum]

Your instr is incorrect, which I guess is why spikepl suggested that you look it up as you will learn more thoroughly that way.

Brian

Ok, thanks sir

 

[jdraw]

Just curious --
What exactly is the "second word" in

2156015 Dunlop SP-30
1756514 Goodyear Sport

I these are Tires/Tyres, then you may want to research "atomic" data/fields; and why manufacturer and model/style could/should be separate fields.

Here's a good article on design principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

 

[thenoisydrum]

Just curious --
What exactly is the "second word" in



I these are Tires/Tyres, then you may want to research "atomic" data/fields; and why manufacturer and model/style could/should be separate fields.

Here's a good article on design principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Hi jdraw,
Yes these are tyres. My 2 examples are how I am receiving the data and I need to extract the manufacturer name. In each case the manufacturer name is the second "word" in the string. As more brands will be introduced I don't want to build a static table that lists the manufacturer names and then do a match that way.....

I'd like to be able to extract this second "word" from the string

Thanks

Drum

 

[pr2-eugin]

Drum, I think what JD meant was.. Is Dunlop the second word Or is Dunlop SP-30 the second word?

 

[thenoisydrum]

Ah, sorry yes - Dunlop would be the second word

 

[pr2-eugin]

Simple and easy way, is to create a function... That involves SPLIT function..

Public Function getCompanyName(tmpStr As String) As String
[COLOR=Green]'*******************************************************
'   Code to get the company name from a Long String.
'
' USAGE Exmaple:
'   ? getCompanyName("2156015 Dunlop SP-30")
'     Dunlop
'
'   SELECT fieldName, getCompanyName(fieldName) AS companyName
'   FROM tableName;
'
'Code Courtesy of
'  Paul Eugin
'*******************************************************[/COLOR]
    Dim compArr() As String
    
    compArr = Split(tmpStr, " ")
    getCompanyName = compArr(1)
End Function

Copy the code into a Module Save it by giving another name.. Compile and then use it in a Query..