I have a form with a treeview in it. I use the treeview to select the
product I want, showing me data on the selected node. I am using code
from a template to build my Treeview with Drag and Drop capabilities.
So I don't understand all of the code. I would consider myself a
moderate Access user. I am able to sort my parent nodes(Organization).
I am even able to sort my child nodes(Products). However, the child
nodes have names like "Product 3" and "Product 12". I want to sort the
fields by their numerical value in the same way a number in a text
field would be done by using Val().

In other words, I want my Child nodes(Products) to sort ascending
like:

Product 3
Product 10
Product 15

My question is first how to do that with a text field that has both
text and numerical value. Secondly, where/when in my treeview code
would I want to use it?

I hope this gives enough information for the question I am asking. I
know it is somewhat general, but I think that what I am asking is sort
of general topic. Any help would be appreciated. Thanks.

-WhathaveIdone?

With no detail about how you're populating your treeview, I can only offer
general advice. I'd create a query that sorts the data in the correct order
(add a calculated field using the Val function and sort on it, possibly in
conjunction with other sort fields for higher level branches), and use that,
rather than unsorted data from a table, as the recordsource from which the
tree is populated.

HTH,

Rob


"WhathaveIdone?" <brasus04 (AT) gmail (DOT) com> wrote

On Jul 22, 7:50*pm, "Rob Parker"
<NOSPAMrobppar... (AT) optusnet (DOT) com.au.FORME> wrote:
How about:

tblProducts
PID AutoNumber
Product Text
----------
1 Product 3
2 Product A
3 Product 10
4 Product 15
5 Product 30B16
6 Product 4A20
7 Null

qryProductSort:
SELECT Product
FROM tblProducts
ORDER BY ProductSort1(Product), IIf(Product IS NOT NULL,
Val(ProductSort2(Product)), Null);

!qryProductSort:
Product
--------
Null
Product 3
Product 4A20
Product 10
Product 15
Product 30B16
Product A
Product A

'Module Code
'----------------
Public Function BeforeAnyDigits(varIn As Variant) As String
Dim intInStr As Integer
Dim strBeforeDigits As String
Dim intSmallest As Integer
Dim I As Integer

If IsNull(varIn) Then
BeforeAnyDigits = ""
Exit Function
End If
intSmallest = Len(varIn) + 1
For I = 0 To 9
intInStr = InStr(1, varIn, CStr(I))
If intInStr > 0 And intInStr < intSmallest Then
intSmallest = intTemp
End If
Next I
BeforeAnyDigits = Left(varIn, intSmallest - 1)
End Function

Public Function ProductSort1(varIn As Variant) As Variant
If IsNull(varIn) Then
ProductSort1 = Null
Exit Function
End If
ProductSort1 = BeforeAnyDigits(varIn)
End Function

Public Function ProductSort2(varIn As Variant) As Variant
If IsNull(varIn) Then
ProductSort2 = Null
Exit Function
End If
ProductSort2 = Right(varIn, Len(varIn) - Len(BeforeAnyDigits(varIn)))
End Function
'----------------

Note that if Val() is used instead inside the ProductSort2() function,
the query will not recognize the intent.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

In text, a 3 is larger than 10, What you can do is pad out zeros:

Product 03
Product 10
Product 15

You should be able to run an Update Query to change the data. If you canot
change the data. You can pad it out with a zero(s) in the query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley



"WhathaveIdone?" <brasus04 (AT) gmail (DOT) com> wrote

I tried these functions and I could not get them to work. the only change Imade was putting actually names in and I defined intTemp as integer so it could read. Im getting hung up on the BeforeAnyDigits function, last line "BeforeAnyDigits = Left(varIn, intSmallest-1)"

I am getting another problem though. No matter how I run this (in a query, in immediate window, changing the formatting of the field), I cannot seem to get the Val Function to work. I get a 0 for everything. Here is an example:

Blue Product 5 Dark 0
Blue Product 5 0
Green Product 0 (which I understand)
Green Product 10 0

Arvin,

I have considered padding out zeros, but the numerical values in the stringare an important part of that products title, so I hesitate a little to dothat. Also, I still have the problem of the Val Function as I stated above..

I know there has to be a way of getting this to work. I can't be the only one who has ever faced this issue.
Again, thanks for suggestions.

-WhathaveIdone?

On Jul 22, 5:49*pm, "WhathaveIdone?" <brasu... (AT) gmail (DOT) com> wrote:
The post by David Fenton:

Merge with Word in A2007
http://groups.google.com/group/comp....4a0d9f4b0be75#

led me to a Google search of 'OLEDB wildcards'.

The StackOverflow post had a link to:

Using the Right Wildcard Characters in SQL Statements
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

which said:

"There's no single-digit wildcard available through ADO that equates
to DAO's pound sign wildcard (#)"

That provided the following grain of truth for my mill:

ORDER BY Format(Product, "######");

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On Jul 22, 5:49*pm, "WhathaveIdone?" <brasu... (AT) gmail (DOT) com> wrote:
Alas, closer inspection shows that the Format(Products, "######")
doesn't work as expected. It's back to the drawing board.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Got it!! Thanks for all the help and suggestions. I am hoping that this will be helpful to anyone who is facing this issue. Here is what I did:
1)Created a function getnumbers (credit given to http://www.dbforums.com/microsoft-ac...t-numbers.html)

Function GetNumbers(MyStr As String) As Double
Dim I As Double
GetNumbers = 0
For I = 1 To Len(MyStr)
If Mid(MyStr, I, 1) Like "#" Then GetNumbers = GetNumbers & Mid(MyStr, I, 1)
Next I
End Function

2)Created a query with the table of information that I wanted to be sorted. qryProd fields:
ProductName
PNum:GetNumbers(ProductName)
ProdPre:InStr(1,[ProductName],[PNum],1)
PFin:Iif([ProdPre]>0,Left$([ProductName],ProdPre]-1),[ProductName])
PVal:Val([PNum])
3)Created a second query to sort the first, with fields:
qrySort fields:
PFin, ascending
PVal, ascending
4)In the code to create the ProductNodes, I created my recordset with:
'Prep Code...
Set rst = CurrentDb.QueryDefs!qrySort.OpenRecordset
'Code After to design the treeview...

Works like a charm! Thanks all!