Best way for sorting text field as numeric? (2 Viewers)

[Garindan]

Does anyone have an idea? Is it 'acceptable' to sort by Val?

 

[Garindan]

Anyone?

 

[yhgtbfk]

Personally, I would create a field called number, and have it hidden in the report. (Can you sort by a hidden field?)

Number Name
1.1 1a
1.2 1b
10 10
15 15
111 111

etc....

You can then sort the report by number, and simply make it an invisible field on the report

 

[Brianwarnock]

Hi
Packing took less time than expected so popped on from home, can't test anything but in your query qbe create 2 fields numval:Val([House/FlatNumber]) and textval:IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)) and sort on those.

If all else fails post a new question in reports outlining your problem and asking what can change the order output from a query at the report stage.

Brian

 

[Rich]

The Order by property for a query has no bearing on a Report, you have to use the Reports sorting/grouping options

 

[Garindan]

Ok i think we're getting there now.

I've simply added a field to the query of:-
House/FlatNumber_Sort: Val([House/FlatNumber])

And then in the report sorted by that field. Is it as simple as that? Do i need to use the other expression???:-

textval:IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1))

because all none number entries are returned as 0, and so appear at the top of the sort anyway. Is the other expression needed? Could someone explain what it does?

 

[Brianwarnock]

The textval expression causes the house names to be sorted alphabetically also that such numbers as say 11a 11b etc will be in the correct order.
I also believe that whilst the order/grouping in the report would take precedence if it existed, if you have none then the orderby clause of the query must give the order of the report, I do not understand why it did notr work for you and without access to your database I cannot guess why.
Brian

 

[DALeffler]

Why not just:

SELECT Temp2.Field1
FROM Temp2
ORDER BY Val([Field1]), Temp2.Field1;

 

[Garindan]

Well the Val expression works fine, and all numbers are sorted properly in the report now, but only if its just numbers .

However, the textval: expression doesn't seem to work properly. Not just in the report, but in genereal, and when tested in the query. If i have for example flat numbers 1a, 1b and 1c, the Val expression returns them all as 1, as it should be, but the textval: expression returns them all as 2, so they are not sorted. Not only that but it seems the customer entries for flat 1a and 1b, etc will not show up in the report, and their orders are all listed with flat 1c's

Anyone know whats going on?

 

[DALeffler]

...but the textval: expression returns them all as 2, so they are not sorted.

Looking back over the posts, I think you're missing a function call in the query. txtVal shoulld be:

Mid([House/FlatNumber],IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)))

and not:

IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1))

That's why txtval is returning a 2. Without the Mid function, the Iif is going to return a number.

But I can see no reason to have this complicated (although exquisite) Where clause in the query. As far as I can tell,

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)));

produces the exact same ordered record set as:

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]), qselCustomerAreaSearch.[House/FlatNumber];

Make two new querys, one for each sql, and compare them side by side.

hth,

 

[Garindan]

Looking back over the posts, I think you're missing a function call in the query. txtVal shoulld be:

Mid([House/FlatNumber],IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)))

Yeah thats right It was in there earlier and i've missed it out at some point. Thanks! Doh!

However, i have a new problem now...... Whilst the expressions work properly, and in a query will have the correct results.....in a report i cannot sort by the Val: expression then by the textval: expression as the report sorts val first (obviously) so 1a 1b and 1c all come back as 1, then that causes them to sort all of the 1's together as one entry Only 1 name and address will be produced in the report with ALL the records of 1a, 1b and 1c

Can i just use one expression to sort by? Would that not work?

 

[Garindan]

I am going to move this to the reports forum now as it seems thats where the problem is as the expression itself works fine in queries, etc.

If anyone has any more ideas please could you let me know? Thanks

The new post is here http://www.access-programmers.co.uk/forums/showthread.php?t=83419

Thanks again for everyones help