Need Help Creating If,Then,Else Statement for Report

[rl78]

Hi All,
I need help in creating an Event Procedure on Format for a report listing employee names. Here is what I need to do. I have two fields in my database (1)Notary (Yes or No dependent on that person being a notary) and (2)Initials (Either has a value or has a Null value-which stores their initials if it is filled in in the database. Here is how I need the names displayed:
Example 1: John J. Doe is a notary and has initials- so his name should be displayed as such John J. Doe (JJD)* - where his initials are in parentheses and the asterisk is the indication of him being a notary. Example 2: Mary Williams is not a notary and does not have initials, so her name should be displayed as Mary Williams. I don't have any code yet, can someone help please?

 

[FancyPrairie]

1. Setup a unbound text box on the report (assume name of text box is txtBox)
2. The code in the OnFormat event of the Details section should look something like this:

Dim strName as string

strName = PersonsNameFromTable
If (HasInitials) then strName = strName & "(" & Initials & &quot
if (Notary) then strName = strName & "*"

txtBox = strName

 

[rl78]

Im a little confused about the
strName = PersonsNameFromTable. In the table named Firm Directory, I have a list of over 700 names, I need these names to come out on the report, but in a way that will display the notary and initials. Thanks

 

[FirstAndGoal4]

I dont't know the names of your fields so FN = first name, MI = middle inital, LN = last name, Title, and Notary.

Modify the Control Source that displays the name as follows (= sign is needed):

=FN & " " & IIF(ISNULL(MI),"",MI) & " " & LN & " " & IIF(Notary = "Y","(" & Title & &quot*","&quot

 

[HFloyd]

Hi rl78,

If you want to, you can set this up either on the report itself (as illustrated by FirstAndGoal4, above) or you can put the data in the query to calculate the correctly formatted name.

Assume you have your report's data source set up to an SQL statement like this:

(subsitute the names of your table and fields)

1 table used: tblNames
Fields:
FirstName = text field
MI = text field
LastName = text field
Initials = text field
Notary = boolean (yes/no) field (like a checkbox)

So, if you are working in the query design grid add four new "fields" with the following expressions in place of field names

Field 1:

FullName: FirstName & " " & IIF(ISNULL(MI),"",MI &" ") & LastName

Field 2:

Asterisk:IIF(Notary = -1, "*","")

Field 3:

Parenthases:IIF(Initials is not null, "(" & Initials & ")","")

Field 4:

NameString:FullName & " " & Parenthases & " " & Asterisk

You can then alphabetize by

NameString

if you want it alpha by firstname, or you can alphabetize by

LastName

.

On your report, just have the one field appear (

NameString

)

All of the four fields could have been combined into a single field using various "IIF"s, but for your clarity, I chose to separate them out.

I hope this helps.

Heather

Ps. Incidentally, if you do have your data normalized such as illustrated above, with the separate fields for the parts of the name (FirstName, MI, and LastName), you could also calculate the initials, as oppsed to storing them in the table:
Field 5:

CalculatedInitials: Ucase(Left(FirstName,1)& Left(MI,1)& Left(LastName,1))

http://www.floydinnovations.com

 

[rl78]

Heather, that worked like a charm, thanks so much. Thanks everyone for your help. Happy New Year.