Instead of quote marks, what?

[bodhi23]

I have a report listing certain information. Because that report is then exported to Excel format for distribution, the name field is concatenated, Last name, first name, preferred name on a new line.

Looks something like this:

=[LastNM]&", "&[FirstNM]& Chr(13)& Chr(10)&[PreferredName]

Which gives a nice little:

Doe, Jonathan
Jon

kind of display.

Now, I wanted to put the preferred name in quotation marks, obviously, Access doesn't like quotation marks, so I can't use those. When I reassessed my expectations, I decided to go into the preferred name field and add single quote marks around the preferred name, which yielded the proper results in the form, which all exported nicely into Excel.

The problem is, that I did not realize at the time, that I use the preferred name in mass emails as well. Receiving an email that says Hi 'Jon'! Is kind of silly, yes? You don't use surrounding quotes in a salutation...

So, let me go back to the original question:
How can you tell Access in a formula in the Control Source, that you want it to place some sort of quotes or brackets around a field, since it won't let you use the usual quotes, parenthesis, or square brackets?

Remember, I'm in a concatenated field, using a bold or italic font option will affect all the text in that field, not just the preferred name. Surely there's a formula for this matter? I've been all over this forum and Microsoft.com and have yet to come across the simple answer for this...

In advance, my thanks for your assistance!

 

[RuralGuy]

Doubling up on the quotes will treat the second quote as a literal so "" will leave one quote in the string.

 

[bodhi23]

Tried that, didn't work. I either get an error if I use """&[PreferredName]&""" or if I use ""[PreferredName]"", I get [PreferredName] printed in each record in the report, without quote marks, of course.

That is the method I've used before to add text elements into the report, but Access doesn't appear to recognize quote marks that way.

Thanks though.

 

[RuralGuy]

You need a sequence of 4 quotes:

=[LastNM] & ", " & [FirstNM] & vbCrLf & """" & [PreferredName] & """"

 

[bodhi23]

I didn't try 4 quote marks, I may have to see what that does.

I did, however, figure it out. Double quotes being among those "special characters", one has to use the Chr function with the ANSI code for the character.

For double quotes, the function is Chr (34)

I stuck that in the expression and it worked like a dream! No one else in my office can appreciate the sense of accomplishment I had when I found that...


You wouldn't believe how difficult it is to find a list of the ANSI character set... geez.

Thanks for the suggestions though, I appreciate the help!