Sorting text field as numeric in report? Please Help (1 Viewer)

Garindan

Registered User.
Local time
Today, 21:21
Joined
May 25, 2004
Messages
250
Hi all, i have been having a problem for a week now i think and wondered if anyone would be able to help.

Basically, i have a text field called 'House/FlatNumber', it had to be text obviously as sometimes there may be flat numbers i.e. 1a, 1b and 1c.

I was trying to find out how to sort them properly in a report. I did have a post in the General forums but i have moved it here now.

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

I was given an expression to use in a query as an 'Order By' clause which worked well, but it would not work in the report.

Then we tried entering the expression as two new fields in the SQL query of the report and sorting on them, however that won't work, for example flat 1a, 1b, and 1c would all return as 1 in the first field, then a, b and c in the second field, but the report would sort all the 1's first and show only one name and address for all three, and then list all the accompanying orders under the one address.

Does anybody have an idea of what i can do to get round this problem? Any help would be greatly appreciated :D

The order by clause was:-
ORDER BY Val([House/FlatNumber]), Mid([House/FlatNumber],IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)));

Then the same thing was tried in two field of the query (which is basically the same thing :rolleyes: ) as the following:-
numval:Val([House/FlatNumber])
&
textval:Mid([House/FlatNumber],IIf(Val([House/FlatNumber])=0,1,Nz(Len(Val([House/FlatNumber]))+1,1)))

Is there any way of just combining the fields, and then i could sort on that and that would work? Any ideas?

Thanks in advance.

Lee.
 

DALeffler

Registered Perpetrator
Local time
Today, 15:21
Joined
Dec 5, 2000
Messages
263
Try this:

SrtFld:String(7 - Len(Format(Val([House/FlatNumber]))),48) & Format(Val([House/FlatNumber])) & [House/FlatNumber]

Add "SrtFld" to your query and sort on that field.

Note: Whats the maximum number of total characters allowed in House/FlatNumber? That's the number that should replace the "7" in the above expression.

hth,

Doug.
 

Garindan

Registered User.
Local time
Today, 21:21
Joined
May 25, 2004
Messages
250
Wow yeah that works perfectly Doug :D

As you can tell i'm only just learning expressions, etc. Can i ask how that expression sorts letters?

I have 20 total characters in House/FlatNumber, to accomodate for the (very few) house names instead of numbers, which might be quite long. I have put 20 in the expression, but it seems that however many numbers or letters there are in the entry it always puts 20 0's in front of it in the sort field, is that right?

Thanks again, thats brilliant! :D
 

Garindan

Registered User.
Local time
Today, 21:21
Joined
May 25, 2004
Messages
250
Actually no i got that wrong, if the record has numbers, i.e. 104, then it'll come back as 17 zero's then 104104 (00000000000000000104104) or if it has letters i.e. 1a it'll have the letters at the end of the 20 i.e 000000000000000000011a, but if the entry is a house name it returns 20 zero's then the house name (00000000000000000000housename). Is that ok?

I mean i'm very happy that it works :D I was just trying to work out whether i needed 20 zero's? As if it only replaces the zero's with numbers and all letters come afterwards, then i only probally need about 5? Because i doubt house numbers get higher than 99999.

Is that right?
 

DALeffler

Registered Perpetrator
Local time
Today, 15:21
Joined
Dec 5, 2000
Messages
263
The expression for SrtFld was just a "brute force" method of adding a field to the query that would sort the report in the desired way.

20 zeros are not necessary. But what is necessary is the maximum number of leading numeric characters present in [House/FlatNumber] for any record in the table. We don't need to know which record of the table has the most leading numeric characters, just the maximum number of leading numeric characters. Understand?

An easy way to get that max number is the DMax function.

In the report header, create a text box with the name

txtMaxLen

Set the Control Source for txtMaxLen

=DMax("Len(Format(Val([House/FlatNumber])))","MyTableName")

Then the calculated field in your report query becomes:

SrtFld:String(Val(Reports!MyReportName!txtMaxLen) - Len(Format(Val([House/FlatNumber]))),48) & Format(Val([House/FlatNumber])) & [House/FlatNumber]

(and now the report query can't be opened unless the report is opened...)

That should cut down the number of zeros in SrtFld to the minimum required for the sort.

See how that works. If that works OK and you're still interested, we can refine this even more...
 

Garindan

Registered User.
Local time
Today, 21:21
Joined
May 25, 2004
Messages
250
Thanks alot Doug :D

I think for now i would be content with just putting 5 for the numeric characters, i very much doubt there would be a house number greater than 99999, and if there was i could fix it as i'm constantly working on the database anyway, but so far it doesn't go above about 300. Do you think that's ok?

Thanks very much for your help again :D

Lee.
 

Brianwarnock

Retired
Local time
Today, 21:21
Joined
Jun 2, 2003
Messages
12,701
Hi
I'm glad you got it sorted but do not understand why the original approach did not work, are you sure that you had no additinal sorting /grouping in the report to "undo" the order produced by the queries Order By?

Brian
 

Garindan

Registered User.
Local time
Today, 21:21
Joined
May 25, 2004
Messages
250
Hi Brian, welcome back :)

Erm... I don't remember you asking if there was any grouping/sorting already in the report. :eek:

Yes there was extra grouping/sorting, the reports pull together info from three different queries, and each one is grouped in different ways for different results, so i guess thats why it wouldn't work from just the query :rolleyes:

I've actually learnt quite a bit in the last couple of weeks, thanks for all your help :D

Do you do databases for a living then? Is it good money, etc? Because i'm learning at the moment, and have quite a good one up and running for a friends business, but actually quite enjoy it. At the moment i do a bit of everything, databases, web design, other design, dvd's, hardware and software support etc.

Thanks again.
 

Brianwarnock

Retired
Local time
Today, 21:21
Joined
Jun 2, 2003
Messages
12,701
Yeah I did mention it after Rich's comment but in fairness to you I guess not as clearly and specifically as I should have, still it's ok now.

I'm officially retired from a career working on Mainframes , and now work part time as an IT general dogs body for a charity, they pay in buttons but I enjoy being at the sharp end after years in management. IT gave me a good well paid career which with its continuing development meant one was always learning and thus having new challenges, I don't know what the current job market is like.

Brian
 

DALeffler

Registered Perpetrator
Local time
Today, 15:21
Joined
Dec 5, 2000
Messages
263
...but so far it doesn't go above about 300. Do you think that's ok?
Do I think it's OK? Most definetly: If it does what you want it to do, how wrong can it be? :)

Glad to help.

The one thing I would add is that we're using built in features of Access to sort in ways not optimized by Access: We're calculating a text string from given data and then sorting by that calculated string.

This is by no means the most effecient, economical or the fastest way to do things, but none of that means it's not the best way to present the data to a human being...

Computers are Soooooo stupid...
 

WayneRyan

AWF VIP
Local time
Today, 21:21
Joined
Nov 19, 2002
Messages
7,122
Garindan,

Just playing with this a bit. See Query1 in the attached.

Wayne
 

Attachments

  • Sort.zip
    20.1 KB · Views: 322

Brianwarnock

Retired
Local time
Today, 21:21
Joined
Jun 2, 2003
Messages
12,701
Hi
had another play, if you don't want to build the strings as suggested by Doug then by using the expression below in your sorting/grouping you should achieve your aims.

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

Brian

Ps you may notice that I have removed the unnecessary NZ
 

Users who are viewing this thread

Top Bottom