Best way for sorting text field as numeric?

G

Garindan

Registered User.
Local time
Today, 06:30
Joined
May 25, 2004
Messages
250
Hi everyone, would anyone be able to help me out?

I would like to ask what would be the best/easiest way to be able to sort a text field as numeric? I have a customer database, and the 'House/Flat Number' field is a text field as it has to contain letters aswell, i.e. 41b, etc, and in a very small amount of cases the customer has a house name instead of a number. The problem is that now i'm starting to build reports, output the data, bla bla and i'm obviously having the sort order come as 1,111,21,210,34,4,5,56, etc.

So i just wanted to ask for some help with this. I'm still learning at the moment so please try not to be too technical with me :o

Is there a way of just having leading zero's? I would not need more than 3 so that would be acceptable, but i guess there's no way of hiding them?

What other ways are there of doing it? Thanks for any help any one can give, it is much appreciated. :)
 
Actually you could use spaces instead.
sortField: Space(5-LEN([MyCol])) & [MyCol]

so given you have none over 5 in length (the 5-LEN portion) your example would create
1, 4, 5, 21, 34, 56, 111, 210 because of leading spaces.
You would NOT have to display the field, just sort by it.
As one possible way
 
As long as the numbers are the leading value, you can use the Val() function to extract just the numbers. You can then get the rest of the characters by using the Len() and Mid() functions:

Select * From YourTable
Order By Nz(Val(YourField),0) as NumVal,
Mid(YourField, Nz(Len(Val(YourField)) +1,1)) as TextVal;
 
Last edited:
Thanks for your replies, i think i have a problem, will it still work considering that there are a few (very small amount) of entries which use house names instead of numbers??

Also, as i said i'm still learning, i'm afraid im not too sure what to do with the statements :o Sorry :o

Thanks again.
 
In the example I posted the Val() function would return null so the Nz() function would convert that to 0 - so all the addresses with no numbers would sort ahead of the addresses with any numbers.
 
Ok that sounds brilliant Pat, thanks a lot :D I'm really sorry, but where do i have to put the statement then? :o :o :o
 
Create a query using the QBE and then switch to SQL view. Add the Order By clause. Make sure you change the column names to your own names.

You can use a querydef any place you would otherwise use a table name.
 
Oh i'm reeeeeeaaallllyyyy sorry! :o But i'm still learning (obviously) and i can't work out how to do it/where to put it :o :confused: Sorry!!! Erm, i have several reports, grouped by street name, and then ordered by the 'house/flat number' field. I know Pat is very busy, so i understand if you cant spare the time. If not could anybody else go through it with me??

Sorry again :o :(
 
I don't think i can attach the database, its already in use and has 1000's of customers in it, sorry :(

Do i need to do it like this....

Make a new query and add the table that holds the 'house/flat number' field, then add the field to the query, then change to sql view and add the statement, insert my field name etc, and save it as something like qselnumbersort, etc. Is that what i need to do at first? If so do i just add the house/flat number field and nothing else?

If that is the correct way to do it, how do i add it to my reports? Do i need to open the record source query and add the table there? Insert the query, and then sort by it?

I hope i'm not sounding really stupid :p Have i got it right or completely wrong? As i said before i'm learning :o

Thanks again.
 
Oh dear I'm about to stick my neck out and disagree with Pat.
Using NZ will produce a left justified field that sorts like text eg 41 32 342 10 14 etc . using her Mid house names will sort starting with the second letter.

Obviously I checked before sticking my head above the parapit :D , the code below worked for me, Val returns 0 if the field starts with text.

SELECT Table1.id, Table1.flda
FROM Table1
ORDER BY Val([flda]), Mid([flda],IIf(Val([flda])=0,1,Nz(Len(Val([flda]))+1,1)));

How to use it?
Your reports are presumably based on a query, just add the ORDER BY clause to the SQL using your own field name in place of flda

Brian
 
Would it be best to enter it in the query which the report is based on, or the SQL Query/record source of the report itself??

I currently have 6 reports, using 4 different queries with the 'house/flat number' field, i.e. area search, town search, and street search. Three of the queries were created just for the reports so it's not a problem to change them, but one uses a query which is also used as a basis for forms, and for the rest of the queries, i'm not sure if i should change this one?

Alternatively, if i entered it in the 'main' query (qselCustomerDetails) would it still work through all the queries and reports which are based on it? Is there a possibility it would cause other problems? qselCustomerDetails is also used on my main forms.

Thanks a lot for all the help!!!!!!! I'm very grateful!!!!!
 
Actually, when entered in qselCustomerDetails theres an error which says 'Data type mismatch in criteria expression'. This didn't happen in the other queries tested, and the field was ordered correctly.
 
Ok i've been trying it out......

In queries (except qselCustomerDetails) it works perfectly, and all is in the correct order with any text entries at the top, etc.

In reports, when i've already added it to the query in which the report is based on, it appears to do nothing?? It's as if it ignores it completely, is this right? I tried a few times, i changed the query, tested it, and then tested the report and the order is still mixed up.

I then added it to the Record Source SQL Statement and it had no effect. Until i checked the 'show' box for the Val and Mid expressions, and then in the sorting of the report i sorted by the Val field. Is that right??
 
Garindan said:
Ok i've been trying it out......

In reports, when i've already added it to the query in which the report is based on, it appears to do nothing?? It's as if it ignores it completely, is this right? I tried a few times, i changed the query, tested it, and then tested the report and the order is still mixed up.

I then added it to the Record Source SQL Statement and it had no effect. Until i checked the 'show' box for the Val and Mid expressions, and then in the sorting of the report i sorted by the Val field. Is that right??
Click to expand...

These 2 statements confuse me, the query on which the report is based is the Record Source query, and yes this is where you add the Order By clause. The only thing as far as I know that can prevent this applying is if in the Report Properties sheet you have an entry in the OrderBy property AND the OrderBy On property set to yes.

I go on holiday at 16:30 GMT today and not back till 21st so I hope you get this sorted, no pun intended.

Brian
 
Hi Brian, have you gone on holiday yet? If so thanks for your help, hope you had a good time :D

If not, i still can't get it working, this is my Query in SQL:-

SELECT qselCustomerAreaSearch.Title, qselCustomerAreaSearch.[FirstName/Initial], qselCustomerAreaSearch.Surname, qselCustomerAreaSearch.[House/FlatNumber], qselCustomerAreaSearch.Street, qselCustomerAreaSearch.Area, qselCustomerAreaSearch.Area2, qselCustomerAreaSearch.[Town/City], qselCustomerAreaSearch.County, qselCustomerAreaSearch.PostCode, qselCustomerAreaSearch.HomePhoneNumber, qselCustomerAreaSearch.Mobile_WorkNumber, qselCustomerPurchases.DateOfPurchase, qselCustomerPurchases.Season, qselCustomerPurchases.TotalCost, tblCustomerPurchasedItems.Product, tblCustomerPurchasedItems.Quantity, qselCustomerAreaSearch.CustomerNumber, qselCustomerPurchases.PurchaseNumber
FROM (qselCustomerAreaSearch INNER JOIN qselCustomerPurchases ON qselCustomerAreaSearch.CustomerNumber = qselCustomerPurchases.CustomerNumber) INNER JOIN tblCustomerPurchasedItems ON qselCustomerPurchases.PurchaseNumber = tblCustomerPurchasedItems.PurchaseNumber
ORDER BY Val([House/FlatNumber]), Mid([House/FlatNumber],IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)));

Any idea's?
 
That looks Ok but then didn't you say that the output from the query was correct but it failed to order properly in the report?
I have no further suggestions than the one I made about the Orderby etc on the reports property sheet.

One straw i might grasp/daft idea that i might try would be to base another query on the complex one listed put the orderby into that and base the form on this latest query , but....

Must go now best of luck

Brian

ps post how you get on I'd like to know when I get back
 
Yes, i have tested the query now and it does work fine so nothing wrong with it, but the report still fails to sort properly.

I did try making another query of the above, and adding the ORDER BY clause in as you suggested and it still produced the same results.

One strange thing, i think i've said before, but if i 'show' the Val and Mid expressions in the query, and then sort by the Val field in the report...it all works!?! It even still sorts the text entries correctly. I'm confused.

Anybody else have an idea?
 
p.s. there is nothing entered in Order By and Order By On is set to No in the form properties sheet.
 
You must log in or register to reply here.

Similar threads

A
Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am
2
Replies
23
Views
2,709
Cotswold
Cotswold
P
long text scrollbar issues
Replies
15
Views
1,044
isladogs
isladogs
T
Question about sorting
Replies
10
Views
1,067
Pat Hartman
P
B
Getting data from differently formatted Excel sheets to a table Form/VBA/Import question
Replies
16
Views
1,244
Pat Hartman
P
W
The records not sorted by the record ID field in ascending order in a continuous form after adding new records
Replies
15
Views
989
wendywu
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom