Problem sorting a combo - leading zeros

[beachy]

Hi,

I am battling with the access sorting on a combo box. I know that on a text field access sorts on the first number rather than the whole number.

As a result on my products combo box the results are displayed as:

Solid American White Oak Flooring - Nature Grade - 100mm x 19mm
Solid American White Oak Flooring - Nature Grade - 115mm x 19mm
Solid American White Oak Flooring - Nature Grade - 130mm x 19mm
Solid American White Oak Flooring - Nature Grade - 75mm x 19mm


I want the products to be sorting according to the width of the boards which is the first number 100, 115, 130, 75 etc....


I have looked at various solutions which dont work. The only solution I can think of is to put leading zeros on the first number detected so that the fields sort correctly.


Obviously I need to ensure that all numbers end up the same length so some number will end up longer than others if I just use a stand 3 leading zeros.


I will also have to have this as another field in my table as I dont want those leading zeros actually appearing in the combo box.



This is the end result I am after:



Solid American White Oak Flooring - Nature Grade - 75mm x 19mm
Solid American White Oak Flooring - Nature Grade - 100mm x 19mm
Solid American White Oak Flooring - Nature Grade - 115mm x 19mm
Solid American White Oak Flooring - Nature Grade - 130mm x 19mm




I am not great with VBA code and I have no idea were to start with coding the solution!


Any help is much appreciated.

 

[David Eagar]

Solid American White Oak Flooring - Nature Grade - 75mm x 19mm

Is this all one field?

 

[beachy]

Yes it is all one field, It is the product title only.

 

[David Eagar]

Possibly split the field in 2 and Description and Dimensions and sort by Dimensions or better still instead of a leading 0, add a space, it will sort and look correctly

 

[beachy]

Hi David thank you for your responses. Unfortunately I cant split the field as I have over 3,000 products in my database and they all follow individual patterns!

I cant always keep the dimentions at the end of the product name!

 

[David Eagar]

I just edited my last post with an attachment

 

[beachy]

You could repost it as an mdb could you? I have only got Access 2003 on my current machine!

 

[DCrake]

And I assume not everything is in metric, if that's the case then you are going to struggle to say the least.

 

[David Eagar]

I might have cracked it a (Right[Description],12) seems to give the result your after

 

[beachy]

You are indeed correct a mix of metric and imperial.

I am beginning to think that it could be very complicated to sort.

I have in the past I have used the =var[] function to sort out the access sorting system but obviously this cant be used in this situation.

Is there another function which would do the same as =var[] but work on the first number within a text field?

 

[beachy]

I might have cracked it a (Right[Description],12) seems to give the result your after

Thanks I will have a look now!

 

[beachy]

Thanks David that works for the group of products but it would not work with the other 2,800 products!

If there is a similar function where I dont have to specify the number of characters and it will pick up the first group of number between to groups of text? And then sort correctly?

 

[David Eagar]

Can you give an example of some of the other products?

 

[gemma-the-husky]

it is sorting text correctly as a dictionary sort. You need to add a "dimensions" field or fields to your dbs - you probably ought to have one anyway - to be able to genuinely sort by reference to the underlying dimension of the product.

you probably also need to add a product group field, as not all products will have dimensions.

its really a matter of deciding how to sort and filter your data - your really don't want to be slicing strings as a rule.

In particular - you shouldn't have this product description field as a primary key, and probably not even as a indexed field, in view of its length.

 

[DCrake]

If you work on the assumption that the first encounter of a numeric characters is the start of the dimensions phase, then you could split the string into two halves.

You would need two functions

Public Function GetPrefix(anyString As String) As String

For I = 1 To Len(AnyString)
   If IsNumeric(Mid(AnyString,I,1)) Then
        GetPrefix = Left(AnyString,I-1)
        Exit For
   End If
Next

End Function

Public Function GetSuffix(anyString As String) As String

For I = 1 To Len(AnyString)
   If IsNumeric(Mid(AnyString,I,1)) Then
        GetSuffix= Mid(AnyString,I)
        Exit For
   End If
Next

End Function

Then in a query Have the following

ProductFullName:[Product]
ProductNameOnly:GetPrefix([Product])
ProductDimensions:GetSufix([Product])

Then sort by ProductNameOnly by ProductDimensions

Untick the columns NameOnly and Dimensions as not shown

 

[David Eagar]

This seems to work using different products and measurements