Now I am stumped.
When I said it worked at home, it was using a sample database like you did.
Small table, small query, simply calculated fields, simple report, and simple
form. I was able to select Rich Text with every combination of calculated
field. (Memo&Memo, Text&Memo, Text&Text, Text&StringConstant&Text,
Text&Number, and so on.) By the way, all the type returns were 10 for all
combinations except a direct reference to a memo field.
When I got back to work and it was not working as expected, I was miffed. I
discovered I was using the same version of Access and SP1. I glared at the
messy and complex database on which I was working and turned away and started
experimenting a little. I used a sample database like you did and as I did
at home, and viola! Everything worked!
So I figured something wrong with my unsimple database, right? Wrong. I
started with a compact and repair. Never know when those hidden indices
might get all whacky. No joy. So, I started small again and worked my way
up. (I used the same populated tables throughout.) When I used a query with
one or two calculated fields, my new forms and reports had no issue with my
selecting Rich Text. When I started expanding and included more calculations
and more fields, keeping it very simple and using the same fresh report, I
hit some threshhold and suddenly lost my ability to choose Rich Text. Even
those fields which had already been using allowing me to select and display
Rich Text now would only show/select Plain Text in the Text Format property.
Oddly though, they somehow still displayed Rich Text when the form was
displayed.
I am thinking bug! *sounds of squeals and feet jumping to chairs*
I am a consistent developer. I did not create any new relationships or
fancy calculations as I expanded my query. Even complex calculated fields I
thought might be an issue still work fine in ones and twos (and still work
when placed directly in the control property of the control itself) but there
is some threshhold beyond which Access gets wiggy (no better word for it)
when referencing text fields from a query. I am not going to bother trying
to pinpoint that threshhold but it's there.
My workaround of putting the calculations in the control property of the
control still works, both in great number and with great complexity.
Calculations from a query just do not give me the option to display Rich Text
beyond a certain point. I am accepting the workaround for now and hoping
Microsoft identifies and slays the bug. It is rather limiting to have to
update each form and report's calculations individually.
Phil.
Allen Browne said:
Phil, I just tried this in Access 2007 (with Service Pack 1.)
Using a table that has a Text field and a rich-text memo field, I typed this
expression as a calculated field in the query:
Result: [MyPlainText] & [MyRichText]
Then in a report based on this query, I was able to set the TextFormat
property of the Result field to Rich Text.
Your experience is different, so let's ask Access how it understands your
field. Open the Immediate Window (Ctrl+G), and enter:
? Currentdb.QueryDefs("Query1").Fields("Result").Type
using your query and field names. The resultant number will be one of the
values in the DAO column here:
http://allenbrowne.com/ser-49.html
e.g. 10 for Text, 12 for Memo, or 9 for Binary.
Post your result, along with the expression for the field in the query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
PJMayo said:
Am I simply missing something or does Access 2007 show an option that is
not
supported?
I see the choice to change a Text Box from Plain Text to Rich Text in a
report, yet Rich Text cannot be selected if I reference text from
calculated
query field, the option is restricted to Plain Text even if part of the
formula includes text from a Rich Text memo.
I can get those same query fields to display in a Rich Text format in the
Datasheet view, so why am I unable to do the same in a report? (I have
not
tried a form yet.) Is there some other property I am missing?
If I instead place the same formula in the control field of that same
report
Text Box, I am able to select Rich Text. This is my current workaround,
but
I prefer to keep calculations in my queries. I do not like having to
repeat
a calculation throughout every relevant form and report. It is a poor
practice.
Thanks,
Phil.
P.S. Please do not refer me to Lebans. I am looking for a 2007 solution
not
a new control.
