Hi,

i invite evenyone to tell me this is not possible, but i think this will present an interesting challenge to the more dedicated gurus... ;-P

i know super/sub- scripting is 'impossible' in text fields for tables. i know there are workarounds for labels in reports and forms. but i need something different than the suggestions i've read here.

what i want

What i want to do is a new approach (for Access) that i saw for an excel spreadsheet functionality to parse a cell and change the formatting appropriately (source: http://www.eng-tips.com/viewthread.cfm?qid=138208). This means the cell (which in access will be a field) is pre-written with formatting instructions. These instructions are as follows:

[] anything between brackets is subscript
{} anything between braces is superscript
// (and my own addition for my purposes) anything between forward slash is italics.

everything else is normal.

for example, in our lab, we have work with genes called:

blaOXA-30-likec, where the formatting would be like this (if you parse it mentally using the rules stated above):

/bla/[OXA-30]-like{/c/}

it is not important for the names to be formatted in the tables or even forms, but for the reports i'd like to be able to have these such names formatted as stated in the instrucitons.

what i've done so far

i have created a table with the following fields ('target' is gene of interest):

TargetID [PK; autonumber]
Target [text] (e.g., "blaOXA-30-likec")
Formatting [text] (this has the same text as "Target", but with typed in instructions (e.g., "/bla/[OXA-30]-like{/c/}"))
Product [text] (gene product upon translation of DNA)
ResistantTo [text] (resistance confered to bacteria once product is present)

i have created a simple tabular report with the fields (based on the table, admittedly, i should probably make the source a query?):

TargetID [Hidden]
Formatting
Product
ResistantTo

and i wish, on the "On Format" event of the Details section, to parse the "Formatting" field and arrange the formatting as specified in the instructions.

The code i have copy-pasted is this, although it needs de-excelling and recoded for access (the original code did not have included for italics, i added that myself by copy-pasting one of the "Do" commands and changing the superscript to italic):


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim NumSub
Dim NumSuper
Dim SubL
Dim SubR
Dim SuperL
Dim SuperR
Dim CheckSub
Dim CounterSub
Dim CheckSuper
Dim CounterSuper
Dim Cell
'
CheckSub = True
CounterSub = 0
CheckSuper = True
CounterSuper = 0
Cell = Formatting
'
NumSub = Len(Cell) - Len(Application.WorksheetFunction.Substitute(Cell, "[", ""))
NumSuper = Len(Cell) - Len(Application.WorksheetFunction.Substitute(Cell, "{", ""))

' format the subscript text
Do
Do While CounterSub <= 1000
SubL = Application.WorksheetFunction.Find("[", ActiveCell, 1)
SubR = Application.WorksheetFunction.Find("]", ActiveCell, 1)
ActiveCell.Characters(SubL, 1).Delete
ActiveCell.Characters(SubR - 1, 1).Delete
ActiveCell.Characters(SubL, SubR - SubL - 1).Font.Subscript = True
CounterSub = CounterSub + 1
If CounterSub = NumSub Then
CheckSub = False
Exit Do
End If
Loop
Loop Until CheckSub = False

' format the superscript text
Do
Do While CounterSuper <= 1000
SuperL = Application.WorksheetFunction.Find("{", ActiveCell, 1)
SuperR = Application.WorksheetFunction.Find("}", ActiveCell, 1)
ActiveCell.Characters(SuperL, 1).Delete
ActiveCell.Characters(SuperR - 1, 1).Delete
ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Superscript = True
CounterSuper = CounterSuper + 1
If CounterSuper = NumSuper Then
CheckSuper = False
Exit Do
End If
Loop
Loop Until CheckSuper = False

' format the italic text
Do
Do While CounterSuper <= 1000
SuperL = Application.WorksheetFunction.Find("{", ActiveCell, 1)
SuperR = Application.WorksheetFunction.Find("}", ActiveCell, 1)
ActiveCell.Characters(SuperL, 1).Delete
ActiveCell.Characters(SuperR - 1, 1).Delete
ActiveCell.Characters(SuperL, SuperR - SuperL - 1).Font.Italic = True
CounterSuper = CounterSuper + 1
If CounterSuper = NumSuper Then
CheckSuper = False
Exit Do
End If
Loop
Loop Until CheckSuper = False

End Sub
can anyone help?

my first question, i suppose would be whether adding any of the brackets or braces or slashes will cause issues in the code in the first place?

and whether anyone can see a way to do this, because i'm too green to see a path at the moment...

(edit: here attached is the report as is currently printing (VBA without On Format invoked, because obviously the code is excel-specific and errors in Access, unsurprisingly)

Few issues:

I wasn't aware that reporting supported such formatting. Are you sure it does or do you intend to use a external source that can handle such formatting?

While you may not be overly concerned with how it is stored, I have to say it will matter. It's easier for database to search with a plaintext; introducing formatting marks will create a great deal of repetitions and make search much more slower. Consider having a field that is a plaintext and another field that is formatted. Strictly speaking, this breaks database normalization and there may be a better way to do it, but I throw this as an example of making searches effective.

As for your code, I wouldn't do three loops for each format, but rather iterate over each character in a string once and modifying based on what was known from previous character (e.g. if I come across a character '{', then I set a flag, position where the first character to be formatted thus) starts, then continue until I encounter the '}' and thus close the string between the position set and clear the flag.

HTH.

You probably have already thought of this but just in case, maybe something like the following would be an option:

http://www.topshareware.com/ValidInputBox-ActiveX-Control-download-36191.htm

hm, not sure that is relevant, KenHigg. it looks like a validator for data, not a formator/debugger...? (but thanks for the suggestion anyway!)

I'm just wondering if this is something you could/should do in an RTF control.

I just fiddled around with VS/C++ to see how easy it might be to create a control that will do what you want. It's just way too much work for me at this point in my life. I stay in the Access world because I'm just too lazy to work that much.

LOL, thanks george, i'll look into the RTF controls... didn't think about that one... HOPEFULLY it'll be easy to implement because i'm lazy too! ;-P

Sory Banana, missed ur post earlier. to answer your questions - i'm absolutely UNsure of whether this is possible or not! LOL.... george's suggestion may yeild a possible solution (haven't had time yet to investigate)

but i have already a plaintext version of the name (see my first post, i have "Target" and "Formatting" as fields in the table, "Target" is the plaintext version, and "formatting" is the marked-up version.)

And thanks for the last suggestion - it will make the code more elegant, i'll just have to figure out how to do it! (may not even need to if the RTF control happens to be what i need)....

ever appreciative of your time,
agnieszka.

You can achieve this in a report using the report Print method and manipulating the font size and the CurrentY value. You can incorporate colour, italics, bold into it also if you want.
Print the characters one by one and reset the CurrentY position each time as it defaults to the bottom right position of the previously printed characted.

I've attached an example here. I haven't done the text manipulation part, where by it reads the string and tells it what kind of text to print etc, but in the code behind the report shows how to print in super / sub script (on one line only at a time).

txtNormal, txtSuper, and txtSub are just invisible unbound controls with relative positions.


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

Me.FontName = "Arial"
Me.FontSize = 10

'print normal text
CurrentX = Me.txtNormal.Left
CurrentY = Me.txtNormal.Top
Me.Print Me.TextA.Value

'print super script
CurrentY = Me.txtSuper.Top
Me.FontSize = 7
Me.Print Me.TextB.Value

'print sub script
CurrentY = Me.txtSub.Top
Me.FontSize = 7
Me.Print Me.TextC.Value

I reckon with a bit of work, you could get this to work easily enough for your sample report.

thanks petehilljnr, i'll check that out tonight. i was also thinking another possibility is to use (somehow) HTML? i'll need to research that, but i saw it in a subject line somewhere RTF vs HTML.... made me think.

That's pretty slick.

You should be able to call a subroutine (from Detail_Format) that parses your string (or just parse it in Detail_Format) and calls another subroutine that does a .Print in the appropriate Y position based on type. You should also be able to call that subroutine with font modifiers (bold, italic, underline).

Thank you PeteHillJnr for the post!

hm, not sure that is relevant, KenHigg. it looks like a validator for data, not a formator/debugger...? (but thanks for the suggestion anyway!)

Sorry for being vague. I was thinking you may find an activeX control to use.

that's ok, but for the record, i'm working on petehilljnr's solution and it looks to wipe the floor with all the other options!! (at first it wasn't working, and i couldn't figure out what i was doing wrong. then i decided to start with a fresh report and it's beaut!)

;-) watch this space!

Cool - Do us a favor and post a sample db when you get it tweaked.

RTF controls eat your heart out!!

this solution, provided by petehilljnr is (in george's words) SLIIIIIIICK!

it even solves the probelm of normalisation because each text portion can simply be concatenated in a query.

also, as it turns out, this arrangement will be useful in other parts of my database ;-)

i did have to tweak the code a little, because if there was no text for that format for that gene, the report would return the word "Null", so i added a check.

anyway, my god i love this forum!!

thanks so much guys, this is the best solution i would ever wish for!

attached are my solutions, and a pdf of the final report ;-)

(also, RTF controls don't do super and sub scripts, which is mostly what i needed this for, but thanks for the suggestions, guys!)

it was also nice to see that the gene names follow a convention of formatting, making this extremely easy, and should work for any additional targets we decide to do.

thanks again to everyone (for anyone wanting a copy of the report/database, look on the last post on the first page of this thread)

Gidday there - glad that idea worked out for you. Any possibility of an Access 2000 copy I could grab a hold of?

sorry! i'm not used to the change in architecture yet.... keep forgetting it's a completely different format!

here we go. ;-) - and unfortunately A2007 doesn't convert all the way back to v97... maybe someone else can convert it for me?

edit:

i've improved the code just a tad (and corrected the spelling of "access-programmers.co.uk", because no one here is a 'prigrammer'...)

the improvement was to dim the normal and small font sizes, so i only had to change this in the one spot at the top of the code.

in addition, i fogot to mention that using Me.Print if i specified to print italics, it would for the next block as well, so i had to 'reset' it at the end of the section before starting the new one. ;-)

Converted and posted because I wanted to take a look, too. It gave an error during conversion about a 'lost library' but didn't see one missing.

In all appearances (stare and compare) it looked the same as the 2k one.

Enjoy what little rep I have you two ... nice job.

For the challenge round ... how about doing it inline with texts? =] Twips to font conversion anyone ... Bueller?

-dK

how about doing it inline with texts? =] Twips to font conversion anyone ... Bueller?I believe the exact quote should be:

Bueller? ... Bueller? ... Bueller? ... Bueller?

Or was it just 3? Not quite sure! :D

thanks dkinley. i don't get missing reference warning when i open the 97 version in 2007, BUT i do get a "covert?" dialog at every step! (also, the target names appear in the print preview, but that whole column is blank in layout view... go figure?)

- oh, and full credit should definitely go to petehilljnr...!

For the challenge round ... how about doing it inline with texts? =] Twips to font conversion anyone ... Bueller?


you mean using some sort of formatting markup like i had thought about at the beginning? that WOULD be nice, especially if in the future a computer illiterate scientist would need to add more targets... BUT, my job here is done ;-P

Exactly! .... I was thinking about Banana's post of using flags.

My contribitution would be a series of textboxes placed on the report tht that are dynamically spaced for the formatting, etc, so it appears to be inline text. I was working on some other stuff in the past where I was concerned about spacing but was thinking that I would need some sort of twips to font conversion so if you chose font A the conversion algorithm would spell properly space the next text box for sub/super text box and then continue the text with a new text box.

There might be another way ... just what I had in my head.

Thanks again, tho!
-dK

That was the original idea for what I was proposing, except without the need for text boxes.
The Print method always hold your place from where the last character was printed, ready to print the next one.
That means you'd only have to reset your Y position (and that's why I had place holders using the Top property as the Y position for super/sub/normal).
Means you could mark up as per original post with perhaps even an evolving set of mark up rules in a seperate table.
It also means you wouldn't have to do a font conversion or anything (though there is TextWidth and TextHeight properties for the Print method).
The only bummer is you can't use this on forms as far as I know. I would be quite nice to test your mark ups in real time, rather than checking the report.

If I get some time this weekend (should do seeing rugby season is over), I'll give it a whack.

Pete.