Combo Box sort first by alpha-numeric address sets

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have a Combo box of addresses that look like this:

Run_point_Address_A

136 Herne Hill, SE24
26 Stanstead Road, SE23
35 Sunderland Road, SE23
389 Coldharbour Lane, SW9
41 Stanstead Road, SE23
Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6


How can I make it sort like this: (Which is Alpha first, then numerical, but
based on the individual alpha address sets)

Run_point_Address_A

Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23
 
You need to get the numbers out of the beginning.

To do this you can use a separate column in your query and use the InStr
function to find the first blank space then use the Mid function to remove
all the data before the blank space, which is the numbers. Afterwards you
can sort on this field.
 
I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string
 
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.
 
Karl,

That is neat code, but what I want to end up with is the street name, then
the number, but sorted on each street group/address; (note the address comes
first, then the number, street per street. The important factor is the
streets must remain together, regardless of whether they have a number or no
number. like this:


Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23



KARL DEWEY said:
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.

--
KARL DEWEY
Build a little - Test a little


efandango said:
I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string
Click to expand...
Click to expand...
 
The field I'm talking about is not the field that you will display in you
combo box. The field I'm talking about is only going to be used to sort the
addresses because you need to remove the numbers to sort it the way you
showed.
 
Ok, I understand, you mean create an extra field from the exisitng fields,
right?

now I have to just get my head around those two funtions that you mentioned...

thanks
 
Karl,

that so nearly does it..., if I sort on one field instead of two (like Tony
suggests in his post), but the postcode at the end of the street name throws
the sort order a little (in relation to the numbers) how can I lost the
postcodes from the streetname?



KARL DEWEY said:
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.

--
KARL DEWEY
Build a little - Test a little


efandango said:
I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string
Click to expand...
Click to expand...
 
Use your standard field for display only.

Use the Street calculated field and the House calculated fields for sorting.
Sort first by Street and then by House.

--
KARL DEWEY
Build a little - Test a little


efandango said:
Karl,

That is neat code, but what I want to end up with is the street name, then
the number, but sorted on each street group/address; (note the address comes
first, then the number, street per street. The important factor is the
streets must remain together, regardless of whether they have a number or no
number. like this:


Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23



KARL DEWEY said:
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.

--
KARL DEWEY
Build a little - Test a little


efandango said:
I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string

:

You need to get the numbers out of the beginning.

To do this you can use a separate column in your query and use the InStr
function to find the first blank space then use the Mid function to remove
all the data before the blank space, which is the numbers. Afterwards you
can sort on this field.

--
Tony Sheehan

I have a Combo box of addresses that look like this:

Run_point_Address_A

136 Herne Hill, SE24
26 Stanstead Road, SE23
35 Sunderland Road, SE23
389 Coldharbour Lane, SW9
41 Stanstead Road, SE23
Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6


How can I make it sort like this: (Which is Alpha first, then numerical,
but
based on the individual alpha address sets)

Run_point_Address_A

Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23
Click to expand...
Click to expand...
Click to expand...
 
This test for ', S' that appears to be in all your postal codes and removes
the PC.

Street:
IIF(InStr([Run_point_Address_A], ", S")>0,
Left(Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A]))))),
InStr([Run_point_Address_A], ", S")-1 ,
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A]))))))

--
KARL DEWEY
Build a little - Test a little


efandango said:
Karl,

that so nearly does it..., if I sort on one field instead of two (like Tony
suggests in his post), but the postcode at the end of the street name throws
the sort order a little (in relation to the numbers) how can I lost the
postcodes from the streetname?



KARL DEWEY said:
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.

--
KARL DEWEY
Build a little - Test a little


efandango said:
I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string

:

You need to get the numbers out of the beginning.

To do this you can use a separate column in your query and use the InStr
function to find the first blank space then use the Mid function to remove
all the data before the blank space, which is the numbers. Afterwards you
can sort on this field.

--
Tony Sheehan

I have a Combo box of addresses that look like this:

Run_point_Address_A

136 Herne Hill, SE24
26 Stanstead Road, SE23
35 Sunderland Road, SE23
389 Coldharbour Lane, SW9
41 Stanstead Road, SE23
Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6


How can I make it sort like this: (Which is Alpha first, then numerical,
but
based on the individual alpha address sets)

Run_point_Address_A

Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23
Click to expand...
Click to expand...
Click to expand...
 
Karl,

Is that to be pasted into the QBE grid as all one line?. I tried that and
got this error:

'The expression you entered has a function has an incorrect number of
arguments'


KARL DEWEY said:
This test for ', S' that appears to be in all your postal codes and removes
the PC.

Street:
IIF(InStr([Run_point_Address_A], ", S")>0,
Left(Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A]))))),
InStr([Run_point_Address_A], ", S")-1 ,
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A]))))))

--
KARL DEWEY
Build a little - Test a little


efandango said:
Karl,

that so nearly does it..., if I sort on one field instead of two (like Tony
suggests in his post), but the postcode at the end of the street name throws
the sort order a little (in relation to the numbers) how can I lost the
postcodes from the streetname?



KARL DEWEY said:
Add another two fields that removes the number and has the number only.

House: IIF(Val([Run_point_Address_A])>0, Val([Run_point_Address_A]), Null)

Street:
Trim(Right([Run_point_Address_A],Len([Run_point_Address_A])-IIf(Val([Run_point_Address_A])=0,0,Len(Val([Run_point_Address_A])))))

Sort on these new fields.

--
KARL DEWEY
Build a little - Test a little


:

I can't remove the numbers, the address has to stay intact because it is used
for comparing to another field in the form. I was hoping that there was
another way of doing with some sort of a 'Sort Criteria' string

:

You need to get the numbers out of the beginning.

To do this you can use a separate column in your query and use the InStr
function to find the first blank space then use the Mid function to remove
all the data before the blank space, which is the numbers. Afterwards you
can sort on this field.

--
Tony Sheehan

I have a Combo box of addresses that look like this:

Run_point_Address_A

136 Herne Hill, SE24
26 Stanstead Road, SE23
35 Sunderland Road, SE23
389 Coldharbour Lane, SW9
41 Stanstead Road, SE23
Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Milkwood Road, SE24
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6


How can I make it sort like this: (Which is Alpha first, then numerical,
but
based on the individual alpha address sets)

Run_point_Address_A

Blythe Hill, SE6
Brockley Rise, SE23
Brockley Rise, SE23
Coldharbour Lane, SE24
389 Coldharbour Lane, SW9
Half Moon Lane, SE24
Herne Hill, SE24
Herne Hill, SE24
136 Herne Hill, SE24
Milkwood Road, SE24
31 Milkwood Road, SE24
67 Milkwood Road, SE24
26 Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE23
Stanstead Road, SE6
41 Stanstead Road, SE23
Sunderland Road, SE23
35 Sunderland Road, SE23
Click to expand...
Click to expand...
Click to expand...
 
Back
Top