MS Access Convert like function to Memo Data Type

Question

I need to use rich text formatting for a field in a report in MS Access in order to use character spacing. In the report, attempting to change the Text Format in the field's properties from Plain Text to Rich Text Produces the error: "The setting you entered isn't valid for this property." I presume that is because the field is not a memo data type, (apparently the only data type which supports RTF). It is in the format "*XXXX_XXXX*" where * and _ are literal characters and the X's represent numbers 0 through 9.

For context: The workflow of this DB is that it has a single form that when specifying an Id, queries a linked table, generates a report based on the query result, and outputs it to PDF.

The field's control source is a field from the query result.

Everything works fine but I need to be able to set this field to RTF. The font is a TrueType font if that makes any difference. Essentially I want to have a fixed height on the field and stretch it horizontally across the width. i.e. in CSS it would be defined roughly as "max-height: 30px; width: 100%;"

Things that I have tried:

In the Query Design View, setting the field's Text Format to Rich Text. I still receive an error when setting the field's Text Format in the Report Design View.

Other idea is to explicitly convert field to the Memo data type (anything over 255 implicitly gets converted to this type), but I am not sure of how to do that in SQl [MS SQL Server we would use something like CONVERT([fieldname], text)]

Would creating a custom format help? I'd imagine not because it would still be plaintext.

Thanks in advance for any help and comments.

Answer 1

Try making the text field the last field in the table. I seem to recall somewhere that you can only have one memo field per table and that memo field must be the last field.

Answer 2

Just to address one of your suggested ideas:

Other idea is to explicitly convert field to the Memo data type (anything over 255 implicitly gets converted to this type), but I am not sure of how to do that in SQl [MS SQL Server we would use something like CONVERT([fieldname], text)]

Sadly, the Access Database Engine does not support Standard SQL's CAST() function :(

Because the Access Database Engine shares an expression service with the VBA5 library (why not VBA6? -- who knows?!), it can use most of the VBA cast functions, however sadly the mapping between data types is not good. The only relevant function is CStr() (cast to String) but how its result are coerced to the various Access Database Engine text data types (variable length VARCHAR, fixed length CHAR, with or without Unicode compression, and MEMO) sadly is undocumented.

There is one exception: the Access Database Engine has its own CDEC() (cast to DECIMAL) function but sadly it has always been completely broken.

Answer 3

You can’t change a BOUND text box to rich text unless the underlying column is a rich text column (memo). However, a simple workaround is place an un-bound text box on the report. Simply set the text to rich text in the property sheet (again, there is no need to use code to try and change this value). Once you have the text box placed on the form, then in the forms “detail-format” which fires for each record, you can in code go:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Me.MyTextBox = "<div><font face=Arial size=4 color=Blue>" & _
                   "<strong>My Blue Bold Text</strong></font></div>"

End Sub

So, you can pull values from other collums in the above code, but keep in mind for reports there must be a corresponding bound control to reference any collum, where as a form does not have this requirement