mail merge - group by query from access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fishpond - 26 Sep 2006 14:10 GMT

I am want to use a query defined in access as my data source for mail merge
in word. When I browse the datasources, any queries in my access database
that include a 'group by' don't show up. All other 'select' queries show as
views as expected.
How can I get the one view I want (which includes the 'group by') to show?

Reply to this Message

Peter Jamieson - 26 Sep 2006 17:36 GMT

Which version of Word/Access? (e.g. I can see simple GROUP BY queiries here
in Word 2003)

Do all of your group by queries have any other "unusual" common
characteristics, e.g. do they all have financial functions, or all use wild
cards?

Peter Jamieson

>I am want to use a query defined in access as my data source for mail merge
> in word. When I browse the datasources, any queries in my access database
> that include a 'group by' don't show up. All other 'select' queries show
> as
> views as expected.
> How can I get the one view I want (which includes the 'group by') to show?

Reply to this Message

Fishpond - 26 Sep 2006 18:33 GMT

Ok - I've done a bit more experimenting, and decided it's not the 'group by'
that is causing the problem.
One of the select fields is "FormatCurrency(Sum(LoanInterest.Interest),2) AS
Interest". If I remove the "FormatCurrency" and just have
"Sum(LoanInterest.Interest),2) AS Interest" then it appears.
The reason I want to format in access and not in Word is because formatting
in word always gives me a space between the currency symbol and the first
digit, and I don't want a space.
Any help would be appreciated.

Thanks

Liz

> Which version of Word/Access? (e.g. I can see simple GROUP BY queiries here
> in Word 2003)
[quoted text clipped - 11 lines]
> > views as expected.
> > How can I get the one view I want (which includes the 'group by') to show?

Reply to this Message

Peter Jamieson - 26 Sep 2006 19:23 GMT

1. Unfortunately, "FormatCurrency" seems to be one of the built-in Access
functions that Word cannot recognise when it gets the data from Access using
its default method (OLEDB). Using Format or FormatNumber doesn't help.
2. However, if you have a mergefield such as

{ MERGEFIELD myamount }

in Word and modify it (use Alt-F9 to show the underlying format) so it looks
like

{ MERGEFIELD myamount \#"$,0.00" }

that may be enough. Not sure why you are getting an extra space?

Peter Jamieson

> Ok - I've done a bit more experimenting, and decided it's not the 'group
> by'
[quoted text clipped - 34 lines]
>> > How can I get the one view I want (which includes the 'group by') to
>> > show?

Reply to this Message

Fishpond - 26 Sep 2006 22:06 GMT

I'll try your suggestion.
Thank you very much for your help.

> 1. Unfortunately, "FormatCurrency" seems to be one of the built-in Access
> functions that Word cannot recognise when it gets the data from Access using
[quoted text clipped - 50 lines]
> >> > How can I get the one view I want (which includes the 'group by') to
> >> > show?

Reply to this Message

Rate this thread: