Apologies if this is old hat...

[R O Tiree]

... but there seems to be a "feature" in Access 2007 when concatenating text strings.

Here's what I'm trying to do: Her Indoors is starting her PhD in Law. The list of publications, journals, etc was huge for her Masters Degree and it's going to be a lot worse writing a thesis of 100k words, so I decided to write her a simple database to keep track of them all. That bit's working fine . The next bit is to concatenate the various text strings in a certain way in order to construct a citation that she can copy/paste for her footnotes.

As long as all the fields are "Plain Text" or "Rich Text" all in the same font/weight/size/name/etc, the & and + operators work perfectly. The snag comes when you try to add fields where there is a change in the font to italics, for example... Access trims any trailing spaces (including the ones you add on purpose) and inserts a hard CR/LF.

Here's what a typical citation should look like:

Dworkin Gerald, Frey R.G. and Bok Sissela, Euthanasia and Physician–Assisted Suicide, Cambridge University Press, Cambridge 1998. pp214-216

And here's what Access ends up with:

Dworkin Gerald, Frey R.G. and Bok Sissela,
Euthanasia and Physician–Assisted Suicide
, Cambridge University Press, Cambridge 1998. pp214-216

Here's the expression:

=[Author] & ", " & [Publication] & ", " & [Publisher] & ", " & [Pub_Place] & ", " & [Pub_Place] & " " & [Pub_Date] & ". pp" & [Pages]

Keep it simple to start with, and I'll be able to mess about with "IIf(Isnull())" and/or null propagations once I've got this first bit running.

Edit: BTW, I've already tried IIf(Isnull()) etc and it's no help... same result.

Clearly, in the short-term, it would be easy enough to copy/paste into her Word doc and then delete the CR/LFs and insert spaces where needed, but this is supposed to make her life easier.

Any clues, please?

 

[spikepl]

If all your fields are plain text, and the output is stored in a field with rich text, you could add your own markup.

Eg try a button that puts this text into a rich text textbox text0:

Text0 = "<STRONG>BBBBBBB</STRONG><EM>BBBBBB</EM>BBBBBBBB"

Maybe there is a smarter way to deal with your problem, but this could be a work-around around any quirks. Unless a better suggestion comes up.

Another patch could be to replace any "," & vbCrLf in your text box with ", " (or perhaps <br/>)you would have to check what is there by temporarily changing the text box property from rich text to plain text.

And one other thing: next time make your title descriptive of your problem. Pleas, wailings and excuses etc contain no useful info, especially so in the title.

 

[R O Tiree]

I'll give it a whirl, thanks. I was hoping to avoid her having to press any buttons... mind you, if that button also happened to copy the result direct to the ClipBoard... hmmm.

 

[spikepl]

I was suggesting that as proof of concept, not for operation of the thing. If that works satsifactorily, then your assignment shown in your first post could simply have the markup added but taking the provisos of my first line into account

 

[R O Tiree]

Working on it now... All text fields are now Plain Text... Writing macro... back in a few...

 

[R O Tiree]

Perfick!!! Thanks a lot, spikepl

Got to write a whole bunch of new subroutines, now, because the format changes depending on what you're trying to cite, but that's just a bunch of typing, really.

You don't happen to have a handy routine about your person to copy the contents of a TextBox to the ClipBoard, so you?

Cheers,

Mike

 

[spikepl]

Look into docmd.RunCommand one of the millions of the runcommand parameters does it, as far as I recall.

 

[R O Tiree]

Found it!

Text41.SetFocus
DoCmd.RunCommand acCmdCopy

 

[R O Tiree]

You are a scholar and a gentleman, Sir.

Many thanks indeed