Group by zip code

[Guest]

Hello,
How do I group my phone directory by 5 digit zip codes, when my zip codes
are in 9 digits?

Thank you in advance for your help.

Ellen

 

[Wayne Morgan]

Create a calculated field in the query, such as:

FirstFive:Left([Table1].[ZipCode], 5)

and use this field to group on.

 

[Guest]

Thanks, Wayne. Your code worked beautifully for the query. When I open the
report, however, it opens a box asking for the zip code. If I simply click
on that box, it opens up the report and it looks fine--except where the zip
codes are blank.

Do you have any suggestions?

Thanks again for your help!
Ellen

Create a calculated field in the query, such as:

FirstFive:Left([Table1].[ZipCode], 5)

and use this field to group on.

--
Wayne Morgan
MS Access MVP

Hello,
How do I group my phone directory by 5 digit zip codes, when my zip codes
are in 9 digits?

 

[Wayne Morgan]

If it opens a box asking for the zip code, then you have a reference to a
field that doesn't or no longer exists in the query. You may need to change
the Control Source of the zip code textbox to the new field, if all you want
is the first five digits displayed. If you still want all nine displayed but
want to group on the first five, than make the calculated field an
additional field (i.e. leave the normal zip code field in the query). Then,
in the Sorting and Grouping dialog in the report, tell it to group on the
calculated field. Once you use the Sorting and Grouping dialog, any sorting
you were doing in the query will be ignored. So, if you want to sort on
other fields also, you will need to add them to this dialog as well.

--
Wayne Morgan
MS Access MVP

Thanks, Wayne. Your code worked beautifully for the query. When I open
the
report, however, it opens a box asking for the zip code. If I simply
click
on that box, it opens up the report and it looks fine--except where the
zip
codes are blank.

Do you have any suggestions?

Thanks again for your help!
Ellen

Create a calculated field in the query, such as:

FirstFive:Left([Table1].[ZipCode], 5)

and use this field to group on.

--
Wayne Morgan
MS Access MVP

Hello,
How do I group my phone directory by 5 digit zip codes, when my zip
codes
are in 9 digits?

 

[Guest]

Thanks, Wayne. I think I've done all you've suggested. Looks like I'll
have to troubleshoot it. As I said, except for that box asking for the zip
code it works beautifully.

Have a good day!!!
Bye,
Ellen

If it opens a box asking for the zip code, then you have a reference to a
field that doesn't or no longer exists in the query. You may need to change
the Control Source of the zip code textbox to the new field, if all you want
is the first five digits displayed. If you still want all nine displayed but
want to group on the first five, than make the calculated field an
additional field (i.e. leave the normal zip code field in the query). Then,
in the Sorting and Grouping dialog in the report, tell it to group on the
calculated field. Once you use the Sorting and Grouping dialog, any sorting
you were doing in the query will be ignored. So, if you want to sort on
other fields also, you will need to add them to this dialog as well.

--
Wayne Morgan
MS Access MVP

Thanks, Wayne. Your code worked beautifully for the query. When I open
the
report, however, it opens a box asking for the zip code. If I simply
click
on that box, it opens up the report and it looks fine--except where the
zip
codes are blank.

Do you have any suggestions?

Thanks again for your help!
Ellen

Create a calculated field in the query, such as:

FirstFive:Left([Table1].[ZipCode], 5)

and use this field to group on.

--
Wayne Morgan
MS Access MVP


Hello,
How do I group my phone directory by 5 digit zip codes, when my zip
codes
are in 9 digits?