sorting data on a continous subform

[Marshall Barton]

Here's the sql from the query:

[quoted text clipped - 48 lines]

Sonning House And Culham House Rochelle Street
Land Between Hq1 And Hq2 Heron Quay

Sort Ascending is the default and has nothing to do with any
error.

You've been mangling the query again
Get rid of the quotes around the 999999

Those multiple properties in a single record are not causing
an overflow for me, but they will never be sorted in a
useful way because that kind of parsing is way beyond
whatever we can come up with here. I suggest that you
manually fix those things by creating new records for each
property.

Just in case there's some difference in your version of
Access and mine, try a more rigorous expression:

ORDER BY IIf([PropNo] Like "#*", Val(CStr([PropNo])),
999999), IIf([PropNo] Like "#*", Mid([PropNo],
Len(CStr(Val([PropNo])))+2), [PropNo])

The above will not sort the records with extra spaces before
or after the number part. But I don't want to go into that
until we get rid of the overflow problem.

Try fixing the query again and run it. If you still get the
overflow, make a new table and copy a few records into it
and reun the query against just a few records. If you can
get that to work, then add more records until you run into
the record that's causing the overflow.

Thanks for the response. I tried what you have suggested no luck, still get
the overflow. The only thing I can think could be causing the overflow is
the unique property reference number which is some 14 and 15 digits long. I
have parsed the address as best I can using your expression as an expression
in a field and not the order by clause in the sql, as for some reason it does
not like that when you try to run the query, and have at last been able to
sort the data. I completed the database today and have installed it for the
users.

Thanks for your help on this.
It is puzzling why I am getting the overflow error. I will reinstall access
and try your suggestion again and see if that helps, as I hate these things
to get the better of me.
Thanks once again for hanging in there with me.

I wish you had posted the query you say is working
along with some of the sample data that you think was
causing the problem before.

OTOH, if you're happy, who am I to complain ;-)