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