"LastName, Firstname" trick (1 Viewer)

[steevie_t]

This is a useful trick: If you use a + to concatenate strings then it propogates nulls, if you use a & it doesn't...

So in the expression:

rec![LastName] & (", " + rec![FirstName])​

If rec![FirstName] is Null then the expression (", " + rec![FirstName]) will be too, but the rec![LastName] & (Null) part won't be. This neatly drops the comma and space if the firstname is Null

Much better than:

rec![LastName] & iif(isnull(rec![FirstName]), "", ", " + rec![FirstName])​

Steve.

 

[Keith Nichols]

Turn trick around?

Hi Steve,

I like the look of your trick.

My database has a telephone listing report that shows Seniority, Discipline, and Position alongside the employees name. Thus an entry might read:

Employee A_____Senior Instrument Engineer

The expression in the query that the report is based on is:

Expr2: [Seniority] & " " & [tbl_Emp_Discipline]![Discipline] & " " & [tbl_Emp_Position]![Position]

This works fine unless the employee has no rank i.e. an "Engineer" rather than a "Senior Engineer" in which case there is a leading space " " which moves the text out of line (this doesn't show up very well here but in a telephone directory it is obvious):

Employee A_____Senior Instrument Engineer
Employee B______Instrument Engineer
Employee C_____Senior Instrument Engineer

Is it possible adapt the trick you gave to remove the null from the first part of an expression?

Kind regards,

Keith.

 

[steevie_t]

Yes, very simple:

([Seniority] + " ") & [tbl_Emp_Discipline]![Discipline] & " " & [tbl_Emp_Position]![Position]

Steve.

 

[Keith Nichols]

Yes, very simple:

([Seniority] + " ") & [tbl_Emp_Discipline]![Discipline] & " " & [tbl_Emp_Position]![Position]

Steve.

Steve,

Works beautifully - thanks.

I'm a slow study so bear with me: Looking at the code and trying to understand what is happening:

([Field1] + " ") gives a null if [Field1] is null

or

([Field1] + " ") gives [Field1] contents plus a space if [Field1] is not null.

([Field1] + " ") & [Field2] gives only [Field2] when the first part is null and all of ([Field1] with space) and [Field2] if it is not null.

I was a bit thrown by the "rec!" in your example as I haven't come across this in my brief Career as an Access Guru wannabe. Broken down into it's bits, I can see that it is pretty easy to use.

Once again - good tip and thanks for the code snippet which helped me understand the usage and smartened up my reports.

Regards,

Keith.

 

[steevie_t]

Ah, the rec! is from my own code. I was working in a vba procedure and had declared rec as a recordset, and yes you are right Null + "Text" gives Null, Null & "Text" gives "Text"

 

[Keith Nichols]

Cool bananas. The more experience I get the easier it will be to work these things out.

I'm glad I came across your tip as I had a mental note to hurt my brain trying to get the whole "if null don't show" thing working. Don't be shy about dropping other good 'tricks' into the repository.

Regards,

Keith.

 

[Pat Hartman]

Reading the help entries on & and + is definitely worthwhile.