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

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!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Possibly in Access 2007/2010. with Rich Text Fields.

For <= A2003 you can try this free app:

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

mx

0
Jarred MeyerProduction ManagerAuthor Commented:
I'm using Access 2010.. Do you know what about the formulas would be?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I have not used that feature, sorry.  Someone probably has ... sit tight ...

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Jarred MeyerProduction ManagerAuthor 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.
0
Jeffrey CoachmanMIS LiasonCommented:
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...
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
0
Jarred MeyerProduction ManagerAuthor Commented:
I'll give the Rich Text a try and get back..

Thanks again for all the input!
0
Jeffrey CoachmanMIS LiasonCommented:
Rich text is what my sample file illustrated, did you test it?
0
Jarred MeyerProduction ManagerAuthor Commented:
Not yet.. Sorry I've been working on a side project.. Probably be getting back to this tomorrow
0
Jarred MeyerProduction ManagerAuthor 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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Jarred MeyerProduction ManagerAuthor 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.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@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.



 
0
Jarred MeyerProduction ManagerAuthor 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..
0
Jeffrey CoachmanMIS LiasonCommented:
<<<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
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
SeyerIT,

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

Be back shortly ...
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jarred MeyerProduction ManagerAuthor 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..
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I don't mind waiting.

Have a Merry Christmas!
0
Jarred MeyerProduction ManagerAuthor Commented:
Still on vacation. Sorry guys. Was hoping to get some resolution before I left.
0
Jarred MeyerProduction ManagerAuthor Commented:
Alright all! I'm back in this today full force!!
0
Jarred MeyerProduction ManagerAuthor 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.
0
Jarred MeyerProduction ManagerAuthor 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!"
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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

0
Jarred MeyerProduction ManagerAuthor 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
0
Jarred MeyerProduction ManagerAuthor 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.. :(
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
My first thought is that is may be related to the Font. Does the Font support those characters?
0
Jarred MeyerProduction ManagerAuthor 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

0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<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
0
Jarred MeyerProduction ManagerAuthor Commented:
I see.. I just wasn't sure if this might be affecting the font symbols and why they are not showing up..
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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



0
Jarred MeyerProduction ManagerAuthor 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)

0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I tried the those alt codes and they appear to work fine in my example.

See attached
 RTDemo2.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
In my example I am only changing the the font.  Check out the report named ContactListRT

0
Jeffrey CoachmanMIS LiasonCommented:
In Print Preview, this is what I get:

untitled.JPG
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
JeffCoachman, Looks like the symbols are working for you.
0
Jarred MeyerProduction ManagerAuthor 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.
0
Jarred MeyerProduction ManagerAuthor 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!
0
Jarred MeyerProduction ManagerAuthor 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!
0
Jeffrey CoachmanMIS LiasonCommented:
<JeffCoachman, Looks like the symbols are working for you.>

OK, I was looking for the colors
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.