[Fishpond]

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?

[Peter Jamieson]

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

"Fishpond" wrote in message
...
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?

[Fishpond]

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

"Peter Jamieson" wrote:

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

"Fishpond" wrote in message
...
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?

[Peter Jamieson]

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

"Fishpond" wrote in message
...
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

"Peter Jamieson" wrote:

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

"Fishpond" wrote in message
...
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?

[Fishpond]

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


"Peter Jamieson" wrote:

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

"Fishpond" wrote in message
...
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

"Peter Jamieson" wrote:

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

"Fishpond" wrote in message
...
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?