Building a txt box from 3 tbl fields...have one question

[pmcleod]

Good day everyone.

BTW - thanks to ALL who have helped me with my first Access project. Especially ANDREW!!!!

Just a quick one - missing the right syntax I guess.

I have a report with a TXT box that builds a name from the surtitle, first, last...fields in the table

My IF statement works perfectly - except...the SurTitle and Accr are lookups in the table and are set as a number field so...

the result of the statement is "10 Bob Smith 5," rather than "Mr. Bob Smith M.A.Sc.,P. Eng.,"

I tried adding "=IIf(IsNull([Prime_ContactSurTitle]),"",[Prime_ContactSurTitle AS xyz_ID_xyz, [SurTitle] AS xyz_DispExpr_xyz] & " ")..."

Am I close?

Can anyone tell me what the right syntax is to get the fields to show as it's text lookup?

CODE:

=IIf(IsNull([Prime_ContactSurTitle]),"",[Prime_ContactSurTitle] & " ") & IIf(IsNull([Prime_ContactFirstName]),"",[Prime_ContactFirstName] & " ") & IIf(IsNull([Prime_ContactLastName]),"",[Prime_ContactLastName] & " ") & IIf(IsNull([Prime_ContactAccr]),"",[Prime_ContactAccr] & ",")

 

[jfgambit]

Why aren't you using a query to feed the information to your report?

 

[pmcleod]

Sorry - I am using a query(qryLtr30). those fields are from the query. I figure rather than pulling in 4 separate bound fields, and having to play with their sizes to get them to look as though they had been typed for all names - long and short (these reports are actually dynamically generated letters based on options selected in a form)...I'd just build a statement that makes it look cleaner as though thr name were typed.

do i make sense?

 

[jfgambit]

Not really....

Are you saying that you have a SurName table that has:

SurNameID
SurNameDesc

Example:
1
Mr

2
Mrs

If so, connect this table to your query for the report and pull in the SurNameDesc instead of the SurNameID...

Or if, I am not understanding you, then please post a sample of your Db and we'll see if we can help

 

[billyr]

A Function?

I like custom functions for anything I may EVER have to solve more than once. (I'm too old to waste time or trust my memory a lot.)

This one takes separate names and creates a single name string for display or reports. It is easily adapable to your case. In fact I have one that accepts separate addr1, addr2, city, st, zip and returns an address string complete with (or without) appropiate commas, spaces, etc. Just a thought.

Public Function FullName(n_First As String, n_Last As String, Optional n_Init As String) As String
On Error GoTo Error_FullName
If IsMissing(n_Init) Then
FullName = Trim(n_First) & " " & Trim(n_Last)
Else
FullName = Trim(n_First) & " " & Trim(n_Init) & " " & Trim(n_Last)
End If
Exit_FullName:
Exit Function
Error_FullName:
FullName = "Name ERROR"
Resume Exit_FullName
End Function