Sorting a concatenated query problem

G

Guest

I am using the following to produce a "stacked Style" name address field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " & IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] & IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) & Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal Code]

The query shows me what I want in datasheet mode (Name and address stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same info...it
sorts by the ON#, not by the city as it shows in the query that I am basing
the report on. I have checked to insure the record source is for the query, I
have tried just producing a report based on the query (automatic), and
nothing is working....

Just to clarify, I want a stacked name address that is sorted alphabetically
by city name....
Thank you for any help,
Bill
 
G

Guest

For reports you need to set the sort in the Sorting and Grouping (under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.
 
G

Guest

Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting or
grouping options? I also scrolled through the report property sheet and could
not find any sort or group options...Sorry, I am really struggling with this
and am only been learning this for a couple of months...please bear with me!
Many Thanks,
Bill

boblarson said:
For reports you need to set the sort in the Sorting and Grouping (under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Billiam said:
I am using the following to produce a "stacked Style" name address field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " & IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] & IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) & Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal Code]

The query shows me what I want in datasheet mode (Name and address stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same info...it
sorts by the ON#, not by the city as it shows in the query that I am basing
the report on. I have checked to insure the record source is for the query, I
have tried just producing a report based on the query (automatic), and
nothing is working....

Just to clarify, I want a stacked name address that is sorted alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...
 
G

Guest

DUH---I see it now under grouping and totals---Bob, how do i get it to sort
the city alphabetically (NOT the name) in the complete stacked name address
group?
Bill
Billiam said:
Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting or
grouping options? I also scrolled through the report property sheet and could
not find any sort or group options...Sorry, I am really struggling with this
and am only been learning this for a couple of months...please bear with me!
Many Thanks,
Bill

boblarson said:
For reports you need to set the sort in the Sorting and Grouping (under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Billiam said:
I am using the following to produce a "stacked Style" name address field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " & IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] & IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) & Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal Code]

The query shows me what I want in datasheet mode (Name and address stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same info...it
sorts by the ON#, not by the city as it shows in the query that I am basing
the report on. I have checked to insure the record source is for the query, I
have tried just producing a report based on the query (automatic), and
nothing is working....

Just to clarify, I want a stacked name address that is sorted alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Make sure the query has the City field in it as a standalone field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Billiam said:
DUH---I see it now under grouping and totals---Bob, how do i get it to
sort
the city alphabetically (NOT the name) in the complete stacked name
address
group?
Bill
Billiam said:
Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting
or
grouping options? I also scrolled through the report property sheet and
could
not find any sort or group options...Sorry, I am really struggling with
this
and am only been learning this for a couple of months...please bear with
me!
Many Thanks,
Bill

boblarson said:
For reports you need to set the sort in the Sorting and Grouping
(under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


:

I am using the following to produce a "stacked Style" name address
field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " &
IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] &
IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) &
Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal
Code]

The query shows me what I want in datasheet mode (Name and address
stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same
info...it
sorts by the ON#, not by the city as it shows in the query that I am
basing
the report on. I have checked to insure the record source is for the
query, I
have tried just producing a report based on the query (automatic),
and
nothing is working....

Just to clarify, I want a stacked name address that is sorted
alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...
Click to expand...
 
F

fredg

DUH---I see it now under grouping and totals---Bob, how do i get it to sort
the city alphabetically (NOT the name) in the complete stacked name address
group?
Bill
Billiam said:
Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting or
grouping options? I also scrolled through the report property sheet and could
not find any sort or group options...Sorry, I am really struggling with this
and am only been learning this for a couple of months...please bear with me!
Many Thanks,
Bill

boblarson said:
For reports you need to set the sort in the Sorting and Grouping (under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

:

I am using the following to produce a "stacked Style" name address field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " & IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] & IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) & Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal Code]

The query shows me what I want in datasheet mode (Name and address stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same info...it
sorts by the ON#, not by the city as it shows in the query that I am basing
the report on. I have checked to insure the record source is for the query, I
have tried just producing a report based on the query (automatic), and
nothing is working....

Just to clarify, I want a stacked name address that is sorted alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...
Click to expand...

If you wish to sort the report by City, you'll have to include a
[City] column in the query in addition to the [City] field you have in
the StackedLastMidFirstAddressCITYALPHA column.

Then, in the report's Sorting and Grouping dialog, set [City] in the
top Field/Expression column, and Ascending in the Sort column.
 
G

Guest

Thank you thank you thank you! The only other thing I did was to set the city
field to not visible as I didn't want it again on the report!
Thanks again very much for your help!

fredg said:
DUH---I see it now under grouping and totals---Bob, how do i get it to sort
the city alphabetically (NOT the name) in the complete stacked name address
group?
Bill
Billiam said:
Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting or
grouping options? I also scrolled through the report property sheet and could
not find any sort or group options...Sorry, I am really struggling with this
and am only been learning this for a couple of months...please bear with me!
Many Thanks,
Bill

:

For reports you need to set the sort in the Sorting and Grouping (under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

:

I am using the following to produce a "stacked Style" name address field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " & IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] & IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) & Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal Code]

The query shows me what I want in datasheet mode (Name and address stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same info...it
sorts by the ON#, not by the city as it shows in the query that I am basing
the report on. I have checked to insure the record source is for the query, I
have tried just producing a report based on the query (automatic), and
nothing is working....

Just to clarify, I want a stacked name address that is sorted alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...

If you wish to sort the report by City, you'll have to include a
[City] column in the query in addition to the [City] field you have in
the StackedLastMidFirstAddressCITYALPHA column.

Then, in the report's Sorting and Grouping dialog, set [City] in the
top Field/Expression column, and Ascending in the Sort column.
Click to expand...
 
G

Guest

Thank you very much for your help--the only other thing i did was to set the
CITY visible property to no as I did not want it to appear on the report as a
seperate item...many thanks for your help! It sure beat my splitting the
concatenated field into name and address, and a second half of city,province
and postal code, and then placing one on top of the other in Report
design...doesn't look so professional, let me tell you!
Best regards and many thanks again!

John Spencer said:
Make sure the query has the City field in it as a standalone field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Billiam said:
DUH---I see it now under grouping and totals---Bob, how do i get it to
sort
the city alphabetically (NOT the name) in the complete stacked name
address
group?
Bill
Billiam said:
Hi Bob,

BTW, I am using Access 2007.

Bob, by View menu item, do you mean the green report icon with view
underneath up in the top left corner , as it does not offer any sorting
or
grouping options? I also scrolled through the report property sheet and
could
not find any sort or group options...Sorry, I am really struggling with
this
and am only been learning this for a couple of months...please bear with
me!
Many Thanks,
Bill

:

For reports you need to set the sort in the Sorting and Grouping
(under the
VIEW menu item). The sorts in queries don't necessarily carry over.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


:

I am using the following to produce a "stacked Style" name address
field for
a report:

StackedLastMidFirstAddressCITYALPHA: Trim([LastName]) & ", " &
Trim([FirstName]) & " " & Trim([MidName]) & "," & Chr(13) & Chr(10) &
[TblFSESO]![Address1] & ", " &
IIf(IsNull([TblFSESO]![Address1]),Null,Chr(13)
& Chr(10)) & [TblFSESO]![Address2] &
IIf(IsNull([TblFSESO]![Address2]),Null,Chr(13) & Chr(10)) &
[TblFSESO]![Address3] &
IIf(IsNull([TblFSESO]![Address3]),Null,Chr(13) &
Chr(10)) & [All Active CITY Alpha sort]![City] & ", " & Chr(13) &
Chr(10) &
[TblFSESO]![Province] & ", " & Chr(13) & Chr(10) & [TblFSESO]![Postal
Code]

The query shows me what I want in datasheet mode (Name and address
stacked
and the city sorted a to z.

When I try to produce a report using the
StackedLastMidFirstAddressCITYALPHA, it does not provide the same
info...it
sorts by the ON#, not by the city as it shows in the query that I am
basing
the report on. I have checked to insure the record source is for the
query, I
have tried just producing a report based on the query (automatic),
and
nothing is working....

Just to clarify, I want a stacked name address that is sorted
alphabetically
by city name....
Thank you for any help,
Bill
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address Envelope with Trim 1
concatenated address fields 5
Address 1
expression to concatenate text strings omitting empty records? 6
Populating textbox with more than one row of text 1
Same expression gets different results in Report and Query 1
Address box with IIf Statement 7
Populating Address fields in form 3

Top