Combo Box sort first by alpha-numeric address sets

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



0
Utf
12/10/2007 8:50:01 PM
access.queries 6343 articles. 1 followers. Follow

11 Replies
992 Views

Similar Articles

[PageSpeed] 53

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

"efandango" <efandango@discussions.microsoft.com> wrote in message 
news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
>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
>
>
> 


0
Tony
12/10/2007 9:23:17 PM
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

"Tony" wrote:

> 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
> 
> "efandango" <efandango@discussions.microsoft.com> wrote in message 
> news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> >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
> >
> >
> > 
> 
> 
> 
0
Utf
12/10/2007 9:44:00 PM
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" wrote:

> 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
> 
> "Tony" wrote:
> 
> > 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
> > 
> > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > >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
> > >
> > >
> > > 
> > 
> > 
> > 
0
Utf
12/10/2007 11:03:00 PM
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" wrote:

> 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" wrote:
> 
> > 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
> > 
> > "Tony" wrote:
> > 
> > > 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
> > > 
> > > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > > >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
> > > >
> > > >
> > > > 
> > > 
> > > 
> > > 
0
Utf
12/10/2007 11:15:01 PM
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.

-- 
Tony Sheehan

"efandango" <efandango@discussions.microsoft.com> wrote in message 
news:8566E1C7-798E-41F3-B515-E3ED2828F632@microsoft.com...
>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
>
> "Tony" wrote:
>
>> 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
>>
>> "efandango" <efandango@discussions.microsoft.com> wrote in message
>> news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
>> >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
>> >
>> >
>> >
>>
>>
>> 


0
Tony
12/10/2007 11:18:46 PM
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

"Tony" wrote:

> 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.
> 
> -- 
> Tony Sheehan
> 
> "efandango" <efandango@discussions.microsoft.com> wrote in message 
> news:8566E1C7-798E-41F3-B515-E3ED2828F632@microsoft.com...
> >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
> >
> > "Tony" wrote:
> >
> >> 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
> >>
> >> "efandango" <efandango@discussions.microsoft.com> wrote in message
> >> news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> >> >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
> >> >
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
12/10/2007 11:41:01 PM
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" wrote:

> 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" wrote:
> 
> > 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
> > 
> > "Tony" wrote:
> > 
> > > 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
> > > 
> > > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > > >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
> > > >
> > > >
> > > > 
> > > 
> > > 
> > > 
0
Utf
12/10/2007 11:49:01 PM
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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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
> > > 
> > > "Tony" wrote:
> > > 
> > > > 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
> > > > 
> > > > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > > > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > > > >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
> > > > >
> > > > >
> > > > > 
> > > > 
> > > > 
> > > > 
0
Utf
12/10/2007 11:59:00 PM
I believe they are in the Access help file.

-- 
Tony Sheehan

"efandango" <efandango@discussions.microsoft.com> wrote in message 
news:88D9864E-D402-4AE1-89F7-ABE731F358B9@microsoft.com...
> 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
>
> "Tony" wrote:
>
>> 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.
>>
>> -- 
>> Tony Sheehan
>>
>> "efandango" <efandango@discussions.microsoft.com> wrote in message
>> news:8566E1C7-798E-41F3-B515-E3ED2828F632@microsoft.com...
>> >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
>> >
>> > "Tony" wrote:
>> >
>> >> 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
>> >>
>> >> "efandango" <efandango@discussions.microsoft.com> wrote in message
>> >> news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
>> >> >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
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
Tony
12/11/2007 12:09:16 AM
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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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
> > > 
> > > "Tony" wrote:
> > > 
> > > > 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
> > > > 
> > > > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > > > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > > > >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
> > > > >
> > > > >
> > > > > 
> > > > 
> > > > 
> > > > 
0
Utf
12/11/2007 12:29:01 AM
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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > 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
> > > > 
> > > > "Tony" wrote:
> > > > 
> > > > > 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
> > > > > 
> > > > > "efandango" <efandango@discussions.microsoft.com> wrote in message 
> > > > > news:1902C3AF-220D-4577-9F28-198B42024D6D@microsoft.com...
> > > > > >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
> > > > > >
> > > > > >
> > > > > > 
> > > > > 
> > > > > 
> > > > > 
0
Utf
12/13/2007 12:01:02 AM
Reply:

Similar Artilces:

Sorting with rules
Hi have faced the following problem: I should sort a big amount of dat according to the the column A (Soils), but NOT in alphabetic order. Th order I need is shown below. I am considering to use some kind of macro which is checking the name of a value in column A(FAO) and if is e. "Lithic leptosol" the row will be moved to the top position and so on Anyone knows a ready made code sample for this kind of purpose? A | B | C FAO | AREA Ha | MAPSHEET Lithic Leptosol | 432...

Summing sets of numbers but with special conditions
Hi - I need a formula to do the following. I have 2 columns of six numbers - for example: A B 1 75 70 2 83 69 3 80 72 4 84 67 5 95 83 6 93 80 The formula needs to do the following: 1. Find the lowest number in column A (75 in row 1 in the above example) 2. Discard the number in corresponding cell in column B (70 in cell B1) 3. From the remaining numbers in column B, discard the highest (83 in cell B5) 4. Add together the remaining 4 numbers in column B (69, 72, 67, 80) and add this result to the number found in step 1 (75) to give a total (363) Any ideas/pointers most w...

how do you hide a name from the globel address book?
in exchange 2K. Thanks ADUC Properties of the user Exch Advanced Check the Hide from Address Lists box On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" <Say-no-to-spam@hotmail.com> wrote: >in exchange 2K. > >Thanks > Thanks. Sorry I missed it. "Andy David - Exchange MVP" <adavid@pleasekeepinngcheesebucket.com> wrote in message news:aii4k014g54q5v7dic5a7r568lq31moanr@4ax.com... > ADUC > Properties of the user > Exch Advanced > Check the Hide from Address Lists box > > > On Fri, 10 Sep 2004 20:34:05 -0400, "Dooma" &...

Outlook 2003
More than 50% of my contacts do not have email addresses, so it therefore clutters the Address book unnecessarily. Is there anyway to allow the address book (the popup which appears when clicking on "To:") to only show contacts that have email addresses? I can use categories to assign contacts as ValidEmailUser, but I can't seem to find a way to force the address book popup form, to show based on a category. Thanks. That's odd. I have never seen the Outlook Address Book display Contacts without electronic addresses. It simply won't do that. Do these Contacts ...

Sorting numbers with multiple decimal points?
Hi - I'm sure this is covered in some docs somewhere, but it's hard to know what to search for. I have a spreadsheet (using Excel 2003 SP1) in which I have a number of rows that have an "ID" field taking the format "X.X.X.X" where X is a number from 0 to 100. Like this: 1.1.0.0 blah blah rest of row 1.1.2.4 blah blah rest of row 1.1.3.0 blah blah rest of row 1.0.0.0 blah blah rest of row 1.1.3.2 blah blah rest of row 1.1.3.1 blah blah rest of row 1.1.1.1 blah blah rest of row 1.1.3.0 blah blah rest of row 1.1.1.3 blah blah rest of row 1.1.2.5 blah blah rest of ...

how to set up a worksheet for unlimited entries?
I need to set up an excel worksheet where the data in the "Cost" colum is added up to show "Total Cost" at the bottom of the column. I can d this very easily for a static range but the "cost" column is no static, it will keep on growing. How do I write a formula for "Tota Cost" which will be aware of how many entries exist in the column s they can be added up. Also, I was going to have "total cost" cell a the bottom of the "cost" column, can "Total Cost" be set up in such way, that the "Total Cost" cell moves dow...

Adding Lines And Boxes, etc. To A Chart ?
Hello, Will blame it on my age. Using Excel 2007 (or, at least trying to) and XP. When I have a chart page opened up, how do I get it to show the bar on the bottom (that the real old versions had) that allows one to click on one of its icons shown to "draw" lines, arrowed lines, boxes, add text with a box, etc on the chart itself ? Thanks, Bob Hi, With the chart selected you can use the Insert tab > Illustrations > Shapes. To add shapes to the chart. Textbox is in the Text group of the Insert tab. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypo...

Sorting rows out of order after all rows are numbered in sequence
Data is in 4 columns with Headers described below: Col A - Line# (rows are numbered in original order in worksheet) Col B - Acct# (property number) Col C - Category (code for the type of information in the rows ie: E, V, X, T) Col D - Label (description of the data or values in the row) There is one row of Expenses (all begin with "LOE - ") that is numbered out of sequence from the other "LOE -" items. I need to Move it up with the other "LOE -" items and if possible Re-sort all the "LOE -" items BUT only the "LOE -" rows within e...

Error 1004
I am creating a macro enabled spreadsheet using Excel 2003. It reads data in from a .csv file and manipulates this data to create several reports. Some of the report users use Excel 97, and get the run time error 'Error 1004 - Unable to set specialcells property' when they try to run the macro that updates the reports from the .csv. I am using the special cells property to find the range that I need to clear when reading in new data. Does anyone know what might be causing this problem? Thanks in advance Hi post the relevant part of your code "Darach" wrote: >...

sum and times within text boxes
I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). I also want sum total (5 text boxes) for grand total. Is this feasible on form?? Thanks On Mon, 14 Jan 2008 18:58:01 -0800, He cries for help <Hecriesforhelp@discussions.microsoft.com> wrote: Have you tried an expression like: =[1box] * [2box] (assuming your control names are 1box and 2box. This expression would go in 3box' Control Source property. -Tom. >I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). >I also want sum total (5 text boxes) for grand total. > >Is this ...

How to send Outlook rule reply to reply-to address (not from address)?
By default Outlook 2002 sends rule-based replies to the From: address of the incoming email message. Is there a way to set-up the reply rule to send to the Reply-to: address instead? Thanks, -Keith ...

Bank Management
Hi, My client uses the Bank Management module, and for some unknown reason they have a problem with one specific user - this user when they go to the Bank Reconcile window do not see any transactions. However as another user, the transactions are clearly visible. Also if the user logs into the problem users machine as themaelves, they too can see the transactions, indicating it is a problem with the user and not machine. Any ideas? Neil Palmer ...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

address book not finding contact when putting in letters
I guess I have general questions about the find function on the top of the toolbar, I have a contact in the company field called netflix, when I type net in the find box, it does not show up, but if I type netflix, it does show up??? why is that , I have noticed this on several contacts Same thing with me I have a name and email address in the Outlook Contacts When sending an email, sometimes as I type the name the email appears, sometimes it does not "bob" <bob@donotspam> wrote in message news:epF4Pp1rEHA.4004@TK2MSFTNGP10.phx.gbl... >I guess I have general questions...

Cannot add customer statement email address in Smartlist
On the Debtor Maintenance Options screen you can chose the checkbox to Send Email Statements to a customer and then enter the To, CC or BCC email addresses. In Smartlist under the Customer folder, while you can choose to add the Send Email Statements column you cannot select to add the To, CC or BCC email address fields. You should be able to do this so you can easily view the information. These columns should be standard columns that you can select for Customers. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most vo...

Check Boxes and Radio buttons on Worksheet
I just discovered that I can drop check box directly on a worksheet. I can make it do what I want, in a single instance, but I was wondering there was a way to make them act like formulas, in that I would like to attach the checkbox directly to a cell physically, and, incorporate it's TRUE/FALSE output in a formula, then be able to copy a whole row or column of cells containing the checkbox and formula ...and other data perhaps in a very general way. in other words havethe control behave as if it were the contents of a cell...Make sense? I don't want to have to write code or ...

Formatting doesn't follow data sort
I was just recently updated to 2003 MS Office Pro. I can't remember what I use to have, but I used to be able to Data Sort info and the borders around cells would follow the appropriate text. It doesn't do that anymore, and I can't find out how to fix it in the online help, and my IT guy doesn't know off the top of his head. Can anyone help or have I wasted a full day and a half putting borders around information for no reason? -- kanimalhouse ------------------------------------------------------------------------ kanimalhouse's Profile: http://www.excelforum.com/me...

Sorting table automatically
I have a set of results which are collected into a table B3:AB23. The data is then sorted by the following macro below. I would like the table to be automatically sorted without me having to use the Keyboard Shortcut: Ctrl+y 30-50 times a day. Can this be done? Sub League() ' ' ' Keyboard Shortcut: Ctrl+y ' Range("B3:AB23").Select Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("J4") _ , Order2:=xlDescending, Key3:=Range("H4"), Order3:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, Mat...

How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first 10)
Using 'Select Top 10' gives me first 10 rows of my table. I need to retrieve the next 10 records. I am writing to an excel spreadsheet where I need to write top 10 rows starting from B2 and next 10 records starting from I2. So how can I split them? Every time I have to split into 10rows each. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1 Something like this will get the job done. I don't know your table/ field names... SELECT TOP 10 primaryKey FROM myTable WHERE primaryKey NOT IN (SELECT TOP...

Wrong Bill To Address
Hi all, I'd like to customize SOP Return Blank form since I discover that the default report from MGP gives the wrong Bill To address (Same as Ship To address). I realise that MGP v8.0 don't store Bill To and Ship To address on the documents so I think the only way is to create a table relationship with "RM Customer Address"? Any one can help me out? Thanks, Sugih Hi Sugih Just a question - When you create a debtor do you change the Bill to Ship to and Statement to - addresses, because I know that if you do not do this it defaults to the Ship to address (its on the ...

Page set up
Is there any way to repeat a row at the BOTTOM of every page (Opposite to repeat row at top). No. This feature is not in the program specifications of Microsoft Excel. ----- Ranjit wrote: ----- Is there any way to repeat a row at the BOTTOM of every page (Opposite to repeat row at top). ...

Pictures not opening. Little red X. email address
My email address should anyone have the answer to the question I posted today about pics not opening in forwarded email. thanks. This is caused by using WORD as your editor and having your options in word set to "Show placeholder" for pictures. Open Word, Select Options and poke around a bit until you see the option, then turn it off. -- Nikki Peterson [MVP - Outlook] "cr" <crrsr@yahoo.com> wrote in message news:00de01c3dc69$5c3c0520$a501280a@phx.gbl... My email address should anyone have the answer to the question I posted today about pics not opening in forw...

Pop Box
I use a third party product that sends emails using outlook express whe an error occurs. I used to use OE 5.** but have since upgraded to IE which means that OE has also been upgraded to OE6. Now when ever I ge an error message I get pop-up box from OE asking me if I wish to sen this email? This never used to appear and I was wondering if there wa anyway to get rid of this feature. I have attached the pop-up messag +---------------------------------------------------------------- | Attachment filename: outlook-send.zip |Download attachment: http://www.outl...

Setting Cell Color within a Cell
I'm using quick test professional which uses Excel DataTables, but with half the functionality. I'm basically exporting a the datatable from Mercury quick test to excel. With in my quick test script I'm performing a compare between two excel documents, and I wish to represent the difference with a Red background. Basically, Is they anyway in which I could add so code within my cell that would automatically change the back ground color. Example: My result is 4.13. I would then change this variable within quicktest to something like vbRed.4.13. Then when it gets sen...

Editing the name box
I entered a name in the name box for the top left cell of a form but when I clicked on the name, the cell showed up at the bottom right of the screen. I was successful in creating a name for a different form (the starting cell shows up at the top left of the screen), but I can't get the first form's starting cell out of the bottom of the screen. Now I have four different names for the same form in the name box. How do I clear the name box of useless names? How do I get the starting cell at the top left of the screen? Insert/Name/Define, then find the name, then click Delete....