Can I have multiple fonts within a concatenated textbox on an Access Report?

Jarred Meyer
Jarred Meyer used Ask the Experts™
on
I want to concatenate three different fields into a text box on a report in Access.. Does anyone know if a textbox can handle multiple fonts or if their is a good way for me to do this?

Let's say texbox source = UCase(FontType([ReqmntArial], "Arial") & " " & FontType([ReqmntWeld], "WeldFont"), etc...

Any ideas?

thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Possibly in Access 2007/2010. with Rich Text Fields.

For <= A2003 you can try this free app:

http://www.lebans.com/richtext.htm

mx

Jarred MeyerProduction Manager

Author

Commented:
I'm using Access 2010.. Do you know what about the formulas would be?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I have not used that feature, sorry.  Someone probably has ... sit tight ...

mx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2011

Commented:
In 2010 you can use a text box to Display Rich Text using html tags.

Here is an example:

Message box replacement sample code
http://blogs.office.com/b/microsoft-access/archive/2008/08/05/message-box-replacement-sample-code.aspx
Top Expert 2011

Commented:
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Turns out to be not so easy, even with Rich Text fields in Access 2010

In addition to having to setting the field datatypes in the table to Memo, and setting the Text format property to "Rich Text", ...trying to concatenate these fields in a report textbox will result in only the first field being displayed, then thee second field will "Wrap" (because of the <Div> tag)

So you may perhaps have to go through some machinations in SQL to modify the HTML, ...or something like that.

Perhaps there is an easier way though...?
(my test db is attached FWIW...)

Again, let's see what other Experts post


;-)

JeffCoachman
Database60.accdb
Jarred MeyerProduction Manager

Author

Commented:
Thanks everyone for the ideas.. I'll keep waiting to see if something pops up.. As of right now I'm just going to bring the fields in as seperate text boxes and will concatenate them if something pops up here.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The issue there, (as I am sure you are aware), is that physically trying to place two textboxes side by side is that you end up with "spacing" issues.

Is functionality a deal-breaker?

In other words, I have never seen anything like what you are asking for here done seamlessly in a report in MS Access.

But I could be wrong...
Top Expert 2011

Commented:
I agree with JeffCoachman that using multiple text boxes has a spacing issue.

I have been using a RTF control for years with VB6 and Access 2003 to display text like you want. In Access 2007/2010 there is now a new Rich Text (not RTF).  I have yet to find a better way than Rich Text to handle what you want to do

Using Rich Text is basically like what web sites do to dynamically generate the HTML for web pages.  

If you are interested in using Rich Text then check out:
Power Tip: Take advantage of Rich Text textboxes in Access
Jarred MeyerProduction Manager

Author

Commented:
I'll give the Rich Text a try and get back..

Thanks again for all the input!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Rich text is what my sample file illustrated, did you test it?
Jarred MeyerProduction Manager

Author

Commented:
Not yet.. Sorry I've been working on a side project.. Probably be getting back to this tomorrow
Jarred MeyerProduction Manager

Author

Commented:
So I've looked through some of the provided websites on the richtext.. Short of me trying to decode this all on my own so I can understand how to implement it is anyone here somewhat familiar with rich text that can give me some pointers on my particular situation?

Thanks
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Again,

...what you seem to be asking for is to format an entire field(s):
<[ReqmntArial] and [ReqmntWeld]>
True "Rich Text" (and RichTextallows you format text as if you were using a Word Processor.

In other words, if you hat this text in a Field called: fldComments

   The Frog is on the pond.

...You could format each letter or word individually

Sure you could format the entire string, but you would have to do this individually, one at a time, for each record.

Again, is this functionality a deal-breaker?

Perhaps there is a way to do this, (in case I am not understanding correctly), but it might require you to actually do some "decoding " on your own, and it might not be what you would call "easy".

Let's see what others may post.

JeffCoachman
Jarred MeyerProduction Manager

Author

Commented:
boag, I wouldn't be able to format individual words, etc.. I basically have 3 different fonts for three different types of characteristics a user will be inputting.. 2 of the fonts could be used at one time where the third would be used by itself (there are three different fields the user is inputting to, to determine which font will display on my datasheet)

I think what I'm going to do is align the two fonts next to each other in two different fields and then just overlay the third field over the top of the other two since it will only display by itself.
Top Expert 2011

Commented:
@SeyerIT:,  Are you still interest in using Rich Text? If so,  do you still want some pointers on your particular situation?

@JeffCoachman
<<Sure you could format the entire string, but you would have to do this individually, one at a time, for each record.>>
I don't see hoe that applies here.



 
Jarred MeyerProduction Manager

Author

Commented:
I'm interested yes if it applies to what I'm doing.. Basically if it can do something that would accomplish what a theoretical text box code = UCase(FontType([ReqmntArial], "Arial") & " " & FontType([ReqmntWeld], "WeldFont"...

If I can concatenate these three fields; (ReqmntArial, ReqmntWeld, ReqmntGDT) together and specify a different font for each one then that accomplishes what I need..
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<<<Sure you could format the entire string, but you would have to do this individually, one at a time, for each record.>>
I don't see hoe that applies here.>
Neither do I.

It seems that the OP want to format two "fields" [ReqmntArial] and [ReqmntWeld]

The Rich text editor I have seen are set up to format the text IN a field, not the entire field.

So I was unsure of how using a utility like the Leban's code would help.

I'll back out now and let the existing experts hash this out, to avoid confusion...

Jeff
Top Expert 2011

Commented:
SeyerIT,

I will create an example for you similar to = UCase(FontType([ReqmntArial], "Arial") & " " & FontType([ReqmntWeld], "WeldFont"

Be back shortly ...
Top Expert 2011
Commented:
I created an example for you:

Check out the report named ContactListRT.  There is a text box control with a control source of:

=FontType([ContactName],1) & ", " & FontType([JobTitle],2) & " at " & FontType([Company],3)

I created a module with this contents:

Option Compare Database
Option Explicit

Public Enum FontList
   fl_ArialBold = 1
   fl_Times = 2
   fl_TimesBold = 3
End Enum

Public Function FontType(pTextToFormat As String, pFont As FontList) As String

Select Case pFont

 Case Is = 1
    FontType = "<font face=""Arial Black"">" & pTextToFormat & "</font>"
 Case Is = 2
    FontType = "<font face=""Times New Roman"">" & pTextToFormat & "</font>"
 Case Is = 3
    FontType = "<font face=""Times New Roman""><strong>" & pTextToFormat & "</strong></font>"
 Case Else
     FontType = pTextToFormat

End Select

End Function

Open in new window


Make sure to set the text box to use the Text Format of Rich Text
Rich Text Property
see attached created with Access 2010

 RTDemo.accdb
Jarred MeyerProduction Manager

Author

Commented:
Thanks coach for the example.. I'm going out of town and won't be back into this now for a week or so.. Hopefully I don't get to much trouble for leaving this hang for a bit..

I'll dig into your example as soon as I get back in..
Top Expert 2011

Commented:
I don't mind waiting.

Have a Merry Christmas!
Jarred MeyerProduction Manager

Author

Commented:
Still on vacation. Sorry guys. Was hoping to get some resolution before I left.
Jarred MeyerProduction Manager

Author

Commented:
Alright all! I'm back in this today full force!!
Jarred MeyerProduction Manager

Author

Commented:
I must be missing something.. And it's probably something simple but when I run my report I'm getting #Type! in my box.. I'm going to keep troubleshooting but if you can think of something that I may have missed let me know..

I checked to be sure my textbox had a control source and it does, I have the module in place and I have the textbox set to Rich Text.
Jarred MeyerProduction Manager

Author

Commented:
So I changed the three fields to be sure my fields weren't causing any problems.. When I changed them all to a field that has data on every line they worked correctly. Then I changed the first field back to one of my requirement fields and only the three lines that have requirements are working and the rest show the #Type! error in them..

Here is a screen shot.
 Fonts
The field in red is the textbox.. The three different requirement fields are being shown in the requirement column. So far I added the requirement field that appears in Char Numbers 5 through 14 and those are the only ones that are showing.. It seems if any of the requirement fields are null then it returns "#Type!"
Top Expert 2011
Commented:
The issue with that you have Null field getting passed to my function that is requiring data.

Try wrapping your field with NZ() like this:

=FontType(Nz([ContactName],""),1) & ", " & FontType(Nz([JobTitle],""),2) & " at " & FontType(Nz([Company].""),3)

or

You could also update my function to handle not data getting passed to it.

Public Function FontType(pTextToFormat As Variant, pFont As FontList) As String

Select Case pFont

 Case Is = 1
    FontType = "<font face=""Arial Black"">" & pTextToFormat & "</font>"
 Case Is = 2
    FontType = "<font face=""Times New Roman"">" & pTextToFormat & "</font>"
 Case Is = 3
    FontType = "<font face=""Times New Roman""><strong>" & pTextToFormat & "</strong></font>"
 Case Else
     FontType = pTextToFormat

End Select

End Function

Open in new window



Note: I create the function only as as example to illustrate using Rich Text. It is not a not a complete solution ready for production. I left out the error handling and other  stuff to keep it simple.
Top Expert 2011
Commented:
or this:

Public Function FontType(pTextToFormat As Variant, pFont As FontList) As String

If IsEmpty(pTextToFormat) Or Nz(pTextToFormat, "") = "" Then
  FontType = ""


Else

    Select Case pFont
    
     Case Is = 1
        FontType = "<font face=""Arial Black"">" & pTextToFormat & "</font>"
     Case Is = 2
        FontType = "<font face=""Times New Roman"">" & pTextToFormat & "</font>"
     Case Is = 3
        FontType = "<font face=""Times New Roman""><strong>" & pTextToFormat & "</strong></font>"
     Case Else
         FontType = pTextToFormat
    
    End Select

End If

End Function

Open in new window

Jarred MeyerProduction Manager

Author

Commented:
That is getting very close!

One question thought.. and I forgot to mention it not realizing that it would be a problem.. When the arial field is being displayed by itself, any symbols ±, °, Ø are be stripped off but when the arial font appears in cunjuction with either of the other two fields then the symbols are showing up.

See this screenshot.. The font in red is the richtext concatenated version and the blue to the left is the seperate fields lined up next to one another..
 symbols
Jarred MeyerProduction Manager

Author

Commented:
oh.. and I tried to change my concatenated formula to:

=FontType(Nz([Requirement (1GDT)],""),1) & " " & [Requirement (Arial)] & "  " & FontType(Nz([Requirement (FCGDT2)],""),3)

Removing the fonttype portion of the formula from the Arial font since it didn't need to be forced.. Thought that may have fixed it but it didn't.. :(
Top Expert 2011

Commented:
My first thought is that is may be related to the Font. Does the Font support those characters?
Jarred MeyerProduction Manager

Author

Commented:
Arial supports the symbols but when I zoom into the red text (where the rich text concatenation is) it looks for some reason as though its not using arial when the Requirement(Arial) appears by itself..
 missing symbols
Let me ask this, In the richText module, what does the Public Enum FontList represent? What is the purpose of it?

Option Compare Database
Option Explicit

Public Enum FontList
   fl_ArialBold = 1
   fl_Times = 2
   fl_TimesBold = 3
End Enum


Public Function FontType(pTextToFormat As String, pFont As FontList) As String

Select Case pFont

 Case Is = 1
    FontType = "<font face=""1GDT-SPC Font"">" & pTextToFormat & "</font>"
 Case Is = 2
    FontType = "<font face=""Arial"">" & pTextToFormat & "</font>"
 Case Is = 3
    FontType = "<font face=""FCGDT2"">" & pTextToFormat & "</font>"
 Case Else
     FontType = pTextToFormat

End Select



End Function

Top Expert 2011

Commented:
<<Let me ask this, In the richText module, what does the Public Enum FontList represent? What is the purpose of it? >>

It is a trick  to help make the parameters of the function a dropdown list when using the function in VBA. This allows intellisense to help you.


 Intellisense
Jarred MeyerProduction Manager

Author

Commented:
I see.. I just wasn't sure if this might be affecting the font symbols and why they are not showing up..
Top Expert 2011

Commented:
The symbols/characters that can be printed  are not control by Access/VBA. Access/VBA can only select the font. It is up to the font to be able to print the symbols/characters
Top Expert 2011

Commented:
I am trying to figure out how to duplicate what you are doing.

How are you entering the symbols/characters into the text filed?  Are you using ALT Codes on the ten-keypad?  


Also try setting the font for every field.like this:

=FontType(Nz([Requirement (1GDT)],""),1) & " " & FontType(Nz([Requirement (Arial)],""),2) & "  " & FontType(Nz([Requirement (FCGDT2)],""),3)

Open in new window



Jarred MeyerProduction Manager

Author

Commented:
Yeh, Alt 216, Alt 176 and Alt 0177.
 I set the formula back and have the same result.. (I did have it setup that way originally but tried to remove the font portion from the arial font just in case that was causing the issue)

Top Expert 2011
Commented:
I tried the those alt codes and they appear to work fine in my example.

See attached
 RTDemo2.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
THTC

What report are you guys referencing?

FWIW I opened all of them in Print Preview and saw no colors on any of them...


JeffCoachman
Top Expert 2011

Commented:
In my example I am only changing the the font.  Check out the report named ContactListRT

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
In Print Preview, this is what I get:

untitled.JPG
Top Expert 2011

Commented:
JeffCoachman, Looks like the symbols are working for you.
Jarred MeyerProduction Manager

Author

Commented:
I tried to set mine up like yours and now I'm getting #Type! for all of the columns where only one Requirement exist. It works if there is something in every Requirement column.. Here is a print preview of the report;

 Printpreview
I changed my formula on the Report to match yours in the example;

=FontType([Requirement (1GDT)],1) & " " & FontType([Requirement (Arial)],2) & "  " & FontType([Requirement (FCGDT2)],3)

My Module is setup like this;

Option Compare Database
Option Explicit

Public Enum FontList
   fl_ArialBold = 1
   fl_Arial = 2
   fl_TimesBold = 3
End Enum

Public Function FontType(pTextToFormat As String, pFont As FontList) As String


If IsEmpty(pTextToFormat) Or Nz(pTextToFormat, "") = "" Then
  FontType = ""


Else

Select Case pFont

 Case Is = 1
    FontType = "<font face=""1GDT-SPC Font"">" & pTextToFormat & "</font>"
 Case Is = 2
    FontType = "<font face=""Arial"">" & pTextToFormat & "</font>"
 Case Is = 3
    FontType = "<font face=""FCGDT2"">" & pTextToFormat & "</font>"
 Case Else
     FontType = pTextToFormat

End Select

End If

End Function

Open in new window




Not sure if this would help, but here is the actual data going into the report..

 data

I wish I could see what the problem is... Only thing I think I can tell about yours from mine is that I'm running all the details in one page with some of them having data in all three columns of the report where as some only fill one or two of the columns.. The ones that only fill one or two of the columns in a single line are the ones not producing the symbols.
Jarred MeyerProduction Manager

Author

Commented:
Ok.. Well it started working when I added the NZ's back into my textbox formula while keeping the If statement in the VBA.. Not sure I understand it but basically adding the  If IsEmpty(pTextToFormat) Or Nz(pTextToFormat, "") = "" Then
  FontType = ""
to my vba the symbols then started showing up..

So to sum up, my textbox formula is:
=FontType(Nz([Requirement (1GDT)],""),1) & " " & FontType(Nz([Requirement (Arial)],""),2) & "  " & FontType(Nz([Requirement (FCGDT2)],""),3)

And my module is:
Option Compare Database
Option Explicit

Public Enum FontList
   fl_ArialBold = 1
   fl_Arial = 2
   fl_TimesBold = 3
End Enum

Public Function FontType(pTextToFormat As String, pFont As FontList) As String


If IsEmpty(pTextToFormat) Or Nz(pTextToFormat, "") = "" Then
  FontType = ""


Else

Select Case pFont

 Case Is = 1
    FontType = "<font face=""1GDT-SPC Font"">" & pTextToFormat & "</font>"
 Case Is = 2
    FontType = "<font face=""Arial"">" & pTextToFormat & "</font>"
 Case Is = 3
    FontType = "<font face=""FCGDT2"">" & pTextToFormat & "</font>"
 Case Else
     FontType = pTextToFormat

End Select

End If

End Function

Open in new window


And now all the symbols are showing up;

 Symbols started working
Again.. I can't explain why adding that section to the module would cause the symbols to start showing up (on top of having the NZ functions in the textbox formula) I had to have both in order for it to work properly..


Thanks for all your help and committment to this issue!
Jarred MeyerProduction Manager

Author

Commented:
This definitely works! And will help my report not be so choppy since I can now concatenate the different fields with different fonts together!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<JeffCoachman, Looks like the symbols are working for you.>

OK, I was looking for the colors

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial