sorting data on a continous subform

[ceebaby via AccessMonster.com]

Hi Folks

I am using access 2000 on xp.
I have a main form with a subform whose underlaying table is the LLPG(land
and property gazeteer) which is list of all properties within a local
authorities boundary. This is set information that I cannot change. This form
enables a user to check if an address to be inputted is valid within the
borough.

I want to sort the building name field ( a text field) which has both numeric
and text information so that it will show all the property numbers first then
the building names next so that it will display property information like
this:

1 anytown rd
2 anytown rd
3 anytown rd etc etc
Arbour Heights building
Astey House
Blackburn House etc etc

I have tried sorting on the query. but the information is all over the place
Is there any code I can use instead ?
Thanks for your help
Ceebaby

 

[Marshall Barton]

I am using access 2000 on xp.
I have a main form with a subform whose underlaying table is the LLPG(land
and property gazeteer) which is list of all properties within a local
authorities boundary. This is set information that I cannot change. This form
enables a user to check if an address to be inputted is valid within the
borough.

I want to sort the building name field ( a text field) which has both numeric
and text information so that it will show all the property numbers first then
the building names next so that it will display property information like
this:

1 anytown rd
2 anytown rd
3 anytown rd etc etc
Arbour Heights building
Astey House
Blackburn House etc etc

I have tried sorting on the query. but the information is all over the place
Is there any code I can use instead ?

You can do this in a query, but you have to parse the
address to get the number part.

SELECT bldg, . . .
FROM LLPG
ORDER BY IIf(bldg Like "#*",Val(bldg),"ZZZ"),
Mid(bldg,IIf(bldg Like "#*",Len(Val(bldg),0)+2) As S2

 

[ceebaby via AccessMonster.com]

I am using access 2000 on xp.
I have a main form with a subform whose underlaying table is the LLPG(land

[quoted text clipped - 17 lines]

I have tried sorting on the query. but the information is all over the place
Is there any code I can use instead ?

You can do this in a query, but you have to parse the
address to get the number part.

SELECT bldg, . . .
FROM LLPG
ORDER BY IIf(bldg Like "#*",Val(bldg),"ZZZ"),
Mid(bldg,IIf(bldg Like "#*",Len(Val(bldg),0)+2) As S2

Thanks Marshall
I am a newbie when it comes to SQL. I have entered your suggestion but get a
syntax error . I have posted the sql

SELECT [TblLLPG-Wards].PropNo, [TblLLPG-Wards].Building, [TblLLPG-Wards].Road,
[TblLLPG-Wards].WardName, [TblLLPG-Wards].PostCode, [TblLLPG-Wards].UPRN
FROM [TblLLPG-Wards]
ORDER BY IIf(Building Like "#*",Val(Building),"ZZZ"),Mid(Building,IIf
(Building Like "#*",Len(Val(Building),0)+2) As S2)

I want to sort the building field which contains the text and numerical data
as mentioned before
Where have I gone wrong ?
Many thanks for anyhelp offered
Ceebaby

 

[Marshall Barton]

I am using access 2000 on xp.
I have a main form with a subform whose underlaying table is the LLPG(land

[quoted text clipped - 17 lines]

I have tried sorting on the query. but the information is all over the place
Is there any code I can use instead ?

You can do this in a query, but you have to parse the
address to get the number part.

SELECT bldg, . . .
FROM LLPG
ORDER BY IIf(bldg Like "#*",Val(bldg),"ZZZ"),
Mid(bldg,IIf(bldg Like "#*",Len(Val(bldg),0)+2) As S2

Thanks Marshall
I am a newbie when it comes to SQL. I have entered your suggestion but get a
syntax error . I have posted the sql

SELECT [TblLLPG-Wards].PropNo, [TblLLPG-Wards].Building, [TblLLPG-Wards].Road,
[TblLLPG-Wards].WardName, [TblLLPG-Wards].PostCode, [TblLLPG-Wards].UPRN
FROM [TblLLPG-Wards]
ORDER BY IIf(Building Like "#*",Val(Building),"ZZZ"),Mid(Building,IIf
(Building Like "#*",Len(Val(Building),0)+2) As S2)

I want to sort the building field which contains the text and numerical data
as mentioned before
Where have I gone wrong ?

You went wrong when you used what I posted without fixing
all my mistakes. Sorry about the mess.

Let's give it another try:

ORDER BY IIf(Building Like "#*", Val(Building), 999999),
IIf(Building Like "#*", Mid(Building, Len(Val(Building))+2),
Building)

BTW, The whole SELECT ... thing is an SQL statement, but
all that glop in the ORDER BY clause are just expressions
that are legal just about anywhere in Access.

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 23 lines]

as mentioned before
Where have I gone wrong ?

You went wrong when you used what I posted without fixing
all my mistakes. Sorry about the mess.

Let's give it another try:

ORDER BY IIf(Building Like "#*", Val(Building), 999999),
IIf(Building Like "#*", Mid(Building, Len(Val(Building))+2),
Building)

BTW, The whole SELECT ... thing is an SQL statement, but
all that glop in the ORDER BY clause are just expressions
that are legal just about anywhere in Access.

Many thanks
Will try this at work tomorrow and let you know how I get on

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 23 lines]

as mentioned before
Where have I gone wrong ?

You went wrong when you used what I posted without fixing
all my mistakes. Sorry about the mess.

Let's give it another try:

ORDER BY IIf(Building Like "#*", Val(Building), 999999),
IIf(Building Like "#*", Mid(Building, Len(Val(Building))+2),
Building)

BTW, The whole SELECT ... thing is an SQL statement, but
all that glop in the ORDER BY clause are just expressions
that are legal just about anywhere in Access.

Still no luck. It is still mixing up the property numbers and the house or
building names. They do not follow on logically

the flat numbers are in a seperate field, but some of these records also
contain text, and the building field contains numerical and text values.
FlatNo Building StreetName
Flat 1 57 Porden House Bromley High Rd

I need flat 1- flat 100 57 Porden House to show
flat 1 57 Porden House
flat 2 57 Porden House
flat A Anfield Buildings
Flat B Anfield Buildings
etc etc
then all the property numbers to show as follows
1
2
3
9
10
11
etc etc


I hope this extra information helps in my quest to sort this out. I can't
beleive how difficult it is for something that really should be straight
forward
Many thanks for time effort and help

 

[Marshall Barton]

Still no luck. It is still mixing up the property numbers and the house or
building names. They do not follow on logically

the flat numbers are in a seperate field, but some of these records also
contain text, and the building field contains numerical and text values.
FlatNo Building StreetName
Flat 1 57 Porden House Bromley High Rd

I need flat 1- flat 100 57 Porden House to show
flat 1 57 Porden House
flat 2 57 Porden House
flat A Anfield Buildings
Flat B Anfield Buildings
etc etc
then all the property numbers to show as follows
1
2
3
9
10
11
etc etc


I hope this extra information helps in my quest to sort this out. I can't
beleive how difficult it is for something that really should be straight
forward

This kind of thing is anything but straightforward. Most of
the trouble is caused by having multiple pices of data
crammed into a single field. What we're stuggling with is
figuring out how to separate the parts (number part and text
part) so we can do different things with each piece.

When you say:

Still no luck. It is still mixing up the
property numbers and the house or
building names. They do not follow
on logically.

you are not communicating enough useful information to help
us work work through whatever problems remain.

The flat number was never in consideration, so let's leave
that out of the discussion/testing until the rest of it is
doing what you want. When I used my suggested ORDER BY
clause in a test query, it did exactly what I think you
want. So, if it wasn't what you want, I have to conclude
that I do not understand the objective or you did not use my
suggestion correctly. Either way, I will need more
information, especially a Copy/Paste of your SQL statement
along with a sample of the result that demonstrates what
isn't working.

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 32 lines]

beleive how difficult it is for something that really should be straight
forward

This kind of thing is anything but straightforward. Most of
the trouble is caused by having multiple pices of data
crammed into a single field. What we're stuggling with is
figuring out how to separate the parts (number part and text
part) so we can do different things with each piece.

When you say:
>Still no luck. It is still mixing up the
>property numbers and the house or
>building names. They do not follow
>on logically.
you are not communicating enough useful information to help
us work work through whatever problems remain.

The flat number was never in consideration, so let's leave
that out of the discussion/testing until the rest of it is
doing what you want. When I used my suggested ORDER BY
clause in a test query, it did exactly what I think you
want. So, if it wasn't what you want, I have to conclude
that I do not understand the objective or you did not use my
suggestion correctly. Either way, I will need more
information, especially a Copy/Paste of your SQL statement
along with a sample of the result that demonstrates what
isn't working.

Marshall thanks again for your time on this.
You have to bear with me as I am self taught and obviously have more gaps in
my knowledge than I thought.

Heres a sample of how the subform is sorting the data:
The first lines are the field names, and comes from an LLPG cvs file not
complied by me. I guess the problem is because they are text fields it is
sorting alphabetically.

Is there any way I can parse the data in the building field by query so that
I get the house names in a seperate field and the numbers in another field.
There are over 200,000 records I dont want to mess around with the original
data.?


FlatNo Building StreetName

Jolles House Bromle High Rd
96 98 Bromle High Rd
Flat 10 95 Bromle High Rd
94 Bromle High Rd

102 Bromle High Rd
100 Bromle High Rd
Warren House Bromle High Rd
Sadler House Bromle High Rd
Flat 3 Bradley House Bromle High Rd
Flat 2 94 Bromle High Rd


Here it the sql from the subforms underlying query

SELECT [TblLLPG-Wards].[Building], [TblLLPG-Wards].[PropNo], [TblLLPG-Wards].
[Road], [TblLLPG-Wards].[WardName], [TblLLPG-Wards].[PostCode], [TblLLPG-
Wards].[UPRN]
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),999999), IIf([PropNo] Like "#*",
Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);


Do you think the problem is due to the fact it is a text field with both text
and numerical data?
Many thanks for any help you can offer

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 26 lines]

along with a sample of the result that demonstrates what
isn't working.

Marshall thanks again for your time on this.
You have to bear with me as I am self taught and obviously have more gaps in
my knowledge than I thought.

Heres a sample of how the subform is sorting the data:
The first lines are the field names, and comes from an LLPG cvs file not
complied by me. I guess the problem is because they are text fields it is
sorting alphabetically.

Is there any way I can parse the data in the building field by query so that
I get the house names in a seperate field and the numbers in another field.
There are over 200,000 records I dont want to mess around with the original
data.?

FlatNo Building StreetName

Jolles House Bromle High Rd
96 98 Bromle High Rd
Flat 10 95 Bromle High Rd
94 Bromle High Rd

102 Bromle High Rd
100 Bromle High Rd
Warren House Bromle High Rd
Sadler House Bromle High Rd
Flat 3 Bradley House Bromle High Rd
Flat 2 94 Bromle High Rd

Here it the sql from the subforms underlying query

SELECT [TblLLPG-Wards].[Building], [TblLLPG-Wards].[PropNo], [TblLLPG-Wards].
[Road], [TblLLPG-Wards].[WardName], [TblLLPG-Wards].[PostCode], [TblLLPG-
Wards].[UPRN]
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),999999), IIf([PropNo] Like "#*",
Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);

Do you think the problem is due to the fact it is a text field with both text
and numerical data?
Many thanks for any help you can offer

also the field in question with both the text and numerical data is the
PropNo field and not the building field as mentioned previously.

 

[Marshall Barton]

Let's give it another try:

[quoted text clipped - 26 lines]

along with a sample of the result that demonstrates what
isn't working.

Heres a sample of how the subform is sorting the data:
The first lines are the field names, and comes from an LLPG cvs file not
complied by me. I guess the problem is because they are text fields it is
sorting alphabetically.

Is there any way I can parse the data in the building field by query so that
I get the house names in a seperate field and the numbers in another field.
There are over 200,000 records I dont want to mess around with the original
data.?

FlatNo Building StreetName

Jolles House Bromle High Rd
96 98 Bromle High Rd
Flat 10 95 Bromle High Rd
94 Bromle High Rd

102 Bromle High Rd
100 Bromle High Rd
Warren House Bromle High Rd
Sadler House Bromle High Rd
Flat 3 Bradley House Bromle High Rd
Flat 2 94 Bromle High Rd

Here it the sql from the subforms underlying query

SELECT [TblLLPG-Wards].[Building], [TblLLPG-Wards].[PropNo], [TblLLPG-Wards].
[Road], [TblLLPG-Wards].[WardName], [TblLLPG-Wards].[PostCode], [TblLLPG-
Wards].[UPRN]
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),999999), IIf([PropNo] Like "#*",
Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);

Do you think the problem is due to the fact it is a text field with both text
and numerical data?
Many thanks for any help you can offer

also the field in question with both the text and numerical data is the
PropNo field and not the building field as mentioned previously.

I guess I just don't know what data we're trying to decipher
here. Everything we've tried was based on sorting the
building field. but now I find it should be the propno
field.

Could you update the example data to include the propno
field so I can see what it looks like? If there's anything
else about that field that I should know, please include
that information too.

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 41 lines]

also the field in question with both the text and numerical data is the
PropNo field and not the building field as mentioned previously.

I guess I just don't know what data we're trying to decipher
here. Everything we've tried was based on sorting the
building field. but now I find it should be the propno
field.

Could you update the example data to include the propno
field so I can see what it looks like? If there's anything
else about that field that I should know, please include
that information too.

sorry to cause the confusion Marshall, the caption for the PropNo field on
the subform is building, and that is where the confusion came from. The
actual field name is PropNo on the LLPG table which is used in the query as
posted above

the data looks as follows: All 3 fields are text fields


FlatNo PropNo StreetName

Jolles House Bromle High Rd
96 98 Bromle High Rd
Flat 10 95 Bromle High Rd
94 Bromle High Rd

102 Bromle High Rd
100 Bromle High Rd
Warren House Bromle High Rd
Sadler House Bromle High Rd
Flat 3 Bradley House Bromle High Rd
Flat 2 94 Bromle High Rd
67 The Blue Pub Bromle High Rd

The PropNo field has both a property number or if the occupant lives in a
flat block it flat block name. So it seems I have 3 lots of data to parse
(before I am sure the sort will work properly)

1) a property nuber such as 94 - this I suppose could be in a seperate field
called PropertyNumber for eg
2) a flat block name such as Bradley House - which could be in a seperate
field called BuildingName for eg
3) some of the data also has a property number and a property name such as
67 The Blue Pub

You initially mentioned I should parse the address, but as you can see the
data seems to fall into one of the 3 categories I mentioned above, and I am
not sure how to split this field into 3 seperate fields ? or do I need to do
that at all

If the PropNo field is parsed properly do you think the sorting on the
subform would sort in the way I need?
ie
1 bromle high Rd
2 "
3 "
Bradley House
Sadler House
Warren House
etc etc

Thanks once again for your help in this and apologies for the initial
confusion, and hope I have made my question a bit clearer.
Ceebaby

 

[Marshall Barton]

sorry to cause the confusion Marshall, the caption for the PropNo field on
the subform is building, and that is where the confusion came from. The
actual field name is PropNo on the LLPG table which is used in the query as
posted above

the data looks as follows: All 3 fields are text fields

FlatNo PropNo StreetName

Jolles House Bromle High Rd
96 98 Bromle High Rd
Flat 10 95 Bromle High Rd
94 Bromle High Rd

102 Bromle High Rd
100 Bromle High Rd
Warren House Bromle High Rd
Sadler House Bromle High Rd
Flat 3 Bradley House Bromle High Rd
Flat 2 94 Bromle High Rd
67 The Blue Pub Bromle High Rd

The PropNo field has both a property number or if the occupant lives in a
flat block it flat block name. So it seems I have 3 lots of data to parse
(before I am sure the sort will work properly)

1) a property nuber such as 94 - this I suppose could be in a seperate field
called PropertyNumber for eg
2) a flat block name such as Bradley House - which could be in a seperate
field called BuildingName for eg
3) some of the data also has a property number and a property name such as
67 The Blue Pub

You initially mentioned I should parse the address, but as you can see the
data seems to fall into one of the 3 categories I mentioned above, and I am
not sure how to split this field into 3 seperate fields ? or do I need to do
that at all

The expressions I posted for the Order By clause are parsing
the PropNo field. I ran my test again and I can't see why
it's not doing what you want.

Let's forget about the form for now and just open the query
directly from the query design window. If that doesn't sort
correctly, please post a sample of the records that
demonstrate what's wrong.

After we get this part working, we'll work on integrating
the Flat into the mix.

 

[ceebaby via AccessMonster.com]

sorry to cause the confusion Marshall, the caption for the PropNo field on
the subform is building, and that is where the confusion came from. The

[quoted text clipped - 33 lines]

not sure how to split this field into 3 seperate fields ? or do I need to do
that at all

The expressions I posted for the Order By clause are parsing
the PropNo field. I ran my test again and I can't see why
it's not doing what you want.

Let's forget about the form for now and just open the query
directly from the query design window. If that doesn't sort
correctly, please post a sample of the records that
demonstrate what's wrong.

After we get this part working, we'll work on integrating
the Flat into the mix.

Thank you Marshall for your response. I tried to open from the query window
and now I get an error message saying "overflow". I have access 2000 sp3
version installed
Any thoughts ?

 

[Marshall Barton]

sorry to cause the confusion Marshall, the caption for the PropNo field on
the subform is building, and that is where the confusion came from. The

[quoted text clipped - 33 lines]

not sure how to split this field into 3 seperate fields ? or do I need to do
that at all

The expressions I posted for the Order By clause are parsing
the PropNo field. I ran my test again and I can't see why
it's not doing what you want.

Let's forget about the form for now and just open the query
directly from the query design window. If that doesn't sort
correctly, please post a sample of the records that
demonstrate what's wrong.

After we get this part working, we'll work on integrating
the Flat into the mix.

Thank you Marshall for your response. I tried to open from the query window
and now I get an error message saying "overflow". I have access 2000 sp3
version installed

Overflow? Now what the bleep is going on?

Either you're using a different query, or you have some kind
weird data in the table. The only way I can get those
expressions to generate an overflow is if the number part of
PropNo is many thousands of digits.

Please post a fresh copy of the query so I can see exactly
what it looks like now.

While I'm looking at that, let's try a couple of other
things. Make a copy of the query and then test the copy.
If the copy still generates the error, remove each
expression from the ORDER BY clause, first one, then the
other and then both. Post back with which one of the
expressions is getting the Overflow error and any other
clues you can see.

Just in case we're seeing some kind of corruption, try
creating a new blank mdb file. Make all of your initial
settings such as unchecking NameAutoCorrect (in Tools -
Options), setting the references, etc. Then use File -
Import to import everything from your current mdb and see if
that makes a difference.

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 17 lines]

and now I get an error message saying "overflow". I have access 2000 sp3
version installed

Overflow? Now what the bleep is going on?

Either you're using a different query, or you have some kind
weird data in the table. The only way I can get those
expressions to generate an overflow is if the number part of
PropNo is many thousands of digits.

Please post a fresh copy of the query so I can see exactly
what it looks like now.

While I'm looking at that, let's try a couple of other
things. Make a copy of the query and then test the copy.
If the copy still generates the error, remove each
expression from the ORDER BY clause, first one, then the
other and then both. Post back with which one of the
expressions is getting the Overflow error and any other
clues you can see.

Just in case we're seeing some kind of corruption, try
creating a new blank mdb file. Make all of your initial
settings such as unchecking NameAutoCorrect (in Tools -
Options), setting the references, etc. Then use File -
Import to import everything from your current mdb and see if
that makes a difference.

Thanks
it seems that once I insert the order by clause, the sort in the query window
automatically changes to ascending, and when I try to view the data I get the
overflow error. When I change the sort to "not sorted" it lets me view the
data with no error message, but this obviously defeats the purpose.

I will try your last suggestion and repost with my findings . You are not
the only one its driving me bleeping crazy too. I would have given up by now,
but I know what the end users are going to be like, so I have to make it as
idiot proof as possible.

Thanks once again for your time on this.
Cheers

 

[Marshall Barton]

sorry to cause the confusion Marshall, the caption for the PropNo field on
the subform is building, and that is where the confusion came from. The

[quoted text clipped - 17 lines]

and now I get an error message saying "overflow". I have access 2000 sp3
version installed

Overflow? Now what the bleep is going on?

Either you're using a different query, or you have some kind
weird data in the table. The only way I can get those
expressions to generate an overflow is if the number part of
PropNo is many thousands of digits.

Please post a fresh copy of the query so I can see exactly
what it looks like now.

While I'm looking at that, let's try a couple of other
things. Make a copy of the query and then test the copy.
If the copy still generates the error, remove each
expression from the ORDER BY clause, first one, then the
other and then both. Post back with which one of the
expressions is getting the Overflow error and any other
clues you can see.

Just in case we're seeing some kind of corruption, try
creating a new blank mdb file. Make all of your initial
settings such as unchecking NameAutoCorrect (in Tools -
Options), setting the references, etc. Then use File -
Import to import everything from your current mdb and see if
that makes a difference.

it seems that once I insert the order by clause, the sort in the query window
automatically changes to ascending, and when I try to view the data I get the
overflow error. When I change the sort to "not sorted" it lets me view the
data with no error message, but this obviously defeats the purpose.

I will try your last suggestion and repost with my findings . You are not
the only one its driving me bleeping crazy too. I would have given up by now,
but I know what the end users are going to be like, so I have to make it as
idiot proof as possible.

Gee, this just isn't that complicated, but I can't seem to
spot where you're going wrong with this.

You failed to post a copy of the query.

I do not understand what you're trying to tell me when you
said "the sort in the query window automatically changes to
ascending". There is no "sort in the query window" in SQL
view, so I don't know what could have changed.

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 36 lines]

but I know what the end users are going to be like, so I have to make it as
idiot proof as possible.

Gee, this just isn't that complicated, but I can't seem to
spot where you're going wrong with this.

You failed to post a copy of the query.

I do not understand what you're trying to tell me when you
said "the sort in the query window automatically changes to
ascending". There is no "sort in the query window" in SQL
view, so I don't know what could have changed.

Thanks
Here's the sql from the query:

SELECT [TblLLPG-Wards].PropNo, [TblLLPG-Wards].Building, [TblLLPG-Wards].Road,
[TblLLPG-Wards].PostCode, [TblLLPG-Wards].UPRN, [TblLLPG-Wards].WardName
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),"999999 "), IIf([PropNo] Like
"#*",Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);

I meant when you look in the design view of the query - the expressions have
ascending in the sort row and that is what seems to cause the overflow. I
have been looking at some of the addresses and some are very long e.g
33-35 Findhorn Street 110-126 134 146 Leven Road 177-195 Abbott Road 1-39
Balmore Close under one ID number. There are several entries like this could
they be causing the problem?

 

[ceebaby via AccessMonster.com]

[quoted text clipped - 36 lines]

but I know what the end users are going to be like, so I have to make it as
idiot proof as possible.

Gee, this just isn't that complicated, but I can't seem to
spot where you're going wrong with this.

You failed to post a copy of the query.

I do not understand what you're trying to tell me when you
said "the sort in the query window automatically changes to
ascending". There is no "sort in the query window" in SQL
view, so I don't know what could have changed.

Thanks
Here's the sql from the query:

SELECT [TblLLPG-Wards].PropNo, [TblLLPG-Wards].Building, [TblLLPG-Wards].Road,
[TblLLPG-Wards].PostCode, [TblLLPG-Wards].UPRN, [TblLLPG-Wards].WardName
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),"999999 "), IIf([PropNo] Like
"#*",Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);

I meant when you look in the design view of the query - the expressions have
ascending in the sort row and that is what seems to cause the overflow. I
have been looking at some of the addresses and some are very long e.g
33-35 Findhorn Street 110-126 134 146 Leven Road 177-195 Abbott Road 1-39
Balmore Close under one ID number. There are several entries like this could
they be causing the problem?

here is some sample data from teh query

Building PropNo Road
25 Harold Godfrey House Louisa Street
229 Cambridge House Mile End Road
253 Albert Stern House Mile End Road
Flat 20 Redclyf House Cephas Street
Flat 1 Timor House Duckett Street
Flat 30 Biscay House Ernest Street
13 Brierly Gardens
11 Merceron Houses Globe Road
13 Merceron Houses Globe Road
14 Merceron Houses Globe Road
15 Merceron Houses Globe Road
17 Merceron Houses Globe Road
18 Merceron Houses Globe Road
Flat 7 Burnham Estate Burnham Street
Flat 21 Burnham Estate Burnham Street
Flat 56 Burnham Estate Globe Road
Flat 57 Burnham Estate Globe Road
Flat 74 Burnham Estate Globe Road
Flat 79 Burnham Estate Globe Road
Flat 2 305 Gretton House Globe Road
Flat 3 305 Gretton House Globe Road
Flat 4 305 Gretton House Globe Road

Site Adjacent
To 404 Bancroft Road 327 Queen Mary And Westfield College Off Mile End Road
Mile End Road
116 Bow Road And Land South Of 116 Bow Road Bow Road
Land Rear Of 23 To 24 Mount Terrace Mount Terrace
Meath Gardens Walter Street
Kiosk Pavement Opposite 207 Marsh Wall Meridian Place
Footpath Land South Of Bracken House Devons Road
Roman Square Roman Road
Sonning House And Culham House Rochelle Street
Land Between Hq1 And Hq2 Heron Quay

 

[Marshall Barton]

Here's the sql from the query:

SELECT [TblLLPG-Wards].PropNo, [TblLLPG-Wards].Building, [TblLLPG-Wards].Road,
[TblLLPG-Wards].PostCode, [TblLLPG-Wards].UPRN, [TblLLPG-Wards].WardName
FROM [TblLLPG-Wards]
ORDER BY IIf([PropNo] Like "#*",Val([PropNo]),"999999 "), IIf([PropNo] Like
"#*",Mid([PropNo],Len(Val([PropNo]))+2),[PropNo]);

I meant when you look in the design view of the query - the expressions have
ascending in the sort row and that is what seems to cause the overflow. I
have been looking at some of the addresses and some are very long e.g
33-35 Findhorn Street 110-126 134 146 Leven Road 177-195 Abbott Road 1-39
Balmore Close under one ID number. There are several entries like this could
they be causing the problem?

here is some sample data from teh query

Building PropNo Road
25 Harold Godfrey House Louisa Street
229 Cambridge House Mile End Road
253 Albert Stern House Mile End Road
Flat 20 Redclyf House Cephas Street
Flat 1 Timor House Duckett Street
Flat 30 Biscay House Ernest Street
13 Brierly Gardens
11 Merceron Houses Globe Road
13 Merceron Houses Globe Road
14 Merceron Houses Globe Road
15 Merceron Houses Globe Road
17 Merceron Houses Globe Road
18 Merceron Houses Globe Road
Flat 7 Burnham Estate Burnham Street
Flat 21 Burnham Estate Burnham Street
Flat 56 Burnham Estate Globe Road
Flat 57 Burnham Estate Globe Road
Flat 74 Burnham Estate Globe Road
Flat 79 Burnham Estate Globe Road
Flat 2 305 Gretton House Globe Road
Flat 3 305 Gretton House Globe Road
Flat 4 305 Gretton House Globe Road

Site Adjacent
To 404 Bancroft Road 327 Queen Mary And Westfield College Off Mile End Road
Mile End Road
116 Bow Road And Land South Of 116 Bow Road Bow Road
Land Rear Of 23 To 24 Mount Terrace Mount Terrace
Meath Gardens Walter Street
Kiosk Pavement Opposite 207 Marsh Wall Meridian Place
Footpath Land South Of Bracken House Devons Road
Roman Square Roman Road
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.

 

[ceebaby via AccessMonster.com]

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.
Best wishes
Ceebaby