"Calan" <calan_svcREMOVE@yaNOSPAMhoo.com> wrote in message news:<P3Jcc.643$FB1.182@fe25.usenetserver.com>...
> I have a text field (called TreeNode) that contains node identifiers for a
> dynamic tree on a web page. For example:
>
> "1.1"
> "1.2"
> "1.2.1"
> "1.2.2"
> ---
> ---
> '1.2.10"
> ---
> etc..
>
> What I need to do is compare this field "value" to another value in a query.
> (I'm using ASP and VBScript to create the statement). For example:
>
> sql = "SELECT SomeField FROM MyTable WHERE TreeNode>=' " & MyNode & " ' "
>
> The problem I run into is when the TreeNode value is say "1.2.10", and it's
> being compared against "1.2.1" and "1.2.2". It should be greater than both
> of these (in my implementation of this), but it actually falls between the
> two, since it is a text comparison.
>
> All I'm really interested in is the last value. But I can't use the RIGHT
> function, because the last value may be one or more digits, and there could
> be any number of levels (periods).
>
> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
>
> sql = "SELECT SomeField FROM MyTable WHERE GetLastValueSQL(TreeNode) >= " &
> GetLastValueASP(MyTreeNode)
>
> This also needs to work in MS Access BTW :-)
>
> Thanks in advance for any help!
>
> Calan
>
> AxMaster Guitar Software
> www.jcsautomation.com
> www.jcsautomation.com/music.asp
> Music software and web design/hosting
>
> "Reality exists only in the minds of the extremely deranged"

> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
>
> This also needs to work in MS Access BTW :-)