Return highest digit from string

[mossimo]

I have a simple access 2003 database that contains links to pictures in a directory.

Example:

link =  [URL unfurl="true"]http://www.mysite.com/upload/MO_07191_[/URL][red]02[/red].jpg
link =  [URL unfurl="true"]http://www.mysite.com/upload/MO_07191_[/URL][red]08[/red].jpg
link =  [URL unfurl="true"]http://www.mysite.com/upload/MO_07191_[/URL][red]05[/red].jpg
link =  [URL unfurl="true"]http://www.mysite.com/upload/MO_07191_[/URL][red]12[/red].jpg

I need to build an sql statement that will return only highest count (this is red part).

I’m really stuck on this any help is welcome

Cheers
mossimo

 

[dhookom]

Try something like:
SELECT Max(Mid(Link,Instr(Link,".jpg")-2),2)) as MaxNum
FROM tblYourTable;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mossimo]

Thanks dhookom,

It works great, I was trying to make it to complex your soulution is simple.

By the way you had a paren out of place should be:
SELECT Max(Mid(Link,Instr(Link,".jpg")-2,2[red])[/red]) as MaxNum

Not picking on you just trying to save someone else the troubleshooting.

Cheers
mossimo

 

[dhookom]

Good catch on the ()s. I usually count the lefts and rights to make sure they match.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.