writing personalized report

[bricklebrit]

Hello,

I was wanting to write a personalized report letter using customer data. I was wanting to do a header like:

"Dear Joe,"

I have the entire customer name in one field and a seperate nickname field. So I would need a command that goes something like:

write "dear" &
IF [nickName] is null, then write first word of [customer] & ","
IF nickname is not null, then write [nickname] & ","

I'm not very good at coding, if anybody could help me out with the syntax, it would be greatly appreciated!

Thanks in advance!

 

[D B Lawson]

Try:

(In an unbound text field)

="Dear " & Iif (IsNull([Nickname]), [Customer], [Nickname]))

Obviously you would need to substitute Nickname and Customer with your proper field names.

 

[bricklebrit]

Hi DB,

Thanks for your help. The sample code you gave me works well!

Is there a simple code to cut just the first word of the name into the [name] slot. So it would be:

="Dear " & Iif (IsNull([Nickname]), [justTheFirstName], [Nickname]))

Unfortunately, I've made some early bad mistakes on designing my database the name for customer's is just [name] which sometimes outputs the filename of the report -- is there some way to search and replace all references to "name" and rename it "CustomerName"? I've made a backup before and attempted to rename this vital data title, but it screws up all my forms, reports, and queries. Any thoughts on that mistake of mine?

Thanks again for all your help!

 

[Jack Cowley]

If you intended to continue to program in Access I would suggest that you invest a very modest sum of money for Find and Replace by Rick Fisher. This program will do just what you and more. I would be lost without it this program. Download a trial version from www.rickworld.com and then send him a cheque.

I forgot this part:

Me.LastName = Trim(Left([FullName], InStr(1, [FullName], " ") - 1))
Me.FirstName = Right(Trim([FullName]), Len(Trim([FullName])) - InStr(1, [FullName], " "))

This code assumes that the first and last names in the FullName field are separated by a space and that the Last Name is first, i.e Bloggs Joe. If a middle initial is thrown in then this isn't going to work. I would suggest that you add another field to your table and use a query and the code above to split your single field into two or three (middle initial?) fields.


[This message has been edited by Jack Cowley (edited 02-19-2002).]

 

[bricklebrit]

Hi Jack,

I apologize for the extremely long delay in responding -- I've had my hands full with an unfortunate hard drive crash! Anyway ...

Thanks for the recommendation on the FindAndReplace -- it work beautifully! I will certainly be purchasing that shareware software right away!

Also the coding works great for writing in the first name. I was wanting an IF statement to check if the customer has a nickname, but am having problems implementing this. Here is the coding I've been able to get so far, but I get an "#error" when indeed the nickname does exist:

="Dear " & IIf(IsNull([NickName]),[NickName],(Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1)))) & ","

Again -- it works flawlessly if the field 'NickName' is empty, but comes up with an error if it has a value.

Thanks again for all your help!

 

[Rich]

Not sure if it's a typo but your Iif's the wrong way around.
="Dear " & IIf(IsNull([NickName]),Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1)))) & ",",[NickName])

 

[bricklebrit]

Hi Rich,

Thanks for your input. I tried the coding you gave me, but it claims it has too many closing parenthsis. When I attempt to fix that it I get another syntax error for not putting text in quotes -- hmmm... I'm really bad at debugging -- could you take another look at it?

Thanks in advance!
Jon

 

[Rich]

Try ="Dear " & IIf(IsNull([NickName]),Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1) & ","),[NickName])

[This message has been edited by Rich (edited 02-28-2002).]

 

[bricklebrit]

I modified it just slightly and it works beautifully! The final code was:

="Dear " & IIf(IsNull([NickName]),Trim(Left([CustomerName],InStr(1,[CustomerName]," ")-1)),[NickName]) & ","

Thanks again for all your help -- I really appreciate it!