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

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

For <= A2003 you can try this free app:

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

mx

I'm using Access 2010.. Do you know what about the formulas would be?

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

mx

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

or this:

http://blog.nkadesign.com/2008/ms-access-enhanced-message-box-replacement/

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

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.

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...

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

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

Thanks again for all the input!

Rich text is what my sample file illustrated, did you test it?

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

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

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

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.

@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.



 

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..

<<<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

SeyerIT,

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

Be back shortly ...

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..

I don't mind waiting.

Have a Merry Christmas!

Still on vacation. Sorry guys. Was hoping to get some resolution before I left.

Alright all! I'm back in this today full force!!

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.

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.
 
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!"

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..
 

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.. :(

My first thought is that is may be related to the Font. Does the Font support those characters?

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..
 
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

<<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.


 

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

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

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)

Select allOpen in new window

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)

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

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

In Print Preview, this is what I get:

untitled.JPG

JeffCoachman, Looks like the symbols are working for you.

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;

 
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

Select allOpen in new window

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

 

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.

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

Select allOpen in new window

And now all the symbols are showing up;

 
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!

This definitely works! And will help my report not be so choppy since I can now concatenate the different fields with different fonts together!

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

OK, I was looking for the colors