?
Solved

Access App Coalesce will not populate on null value.

Posted on 2016-09-15
14
Medium Priority
?
30 Views
Last Modified: 2016-09-25
I have an access App that has a list of employee.  I have a field set to combine First, Last, and Nickname, however if the employee does not have a nickname, the name does not show up at all on the datasheet.

This is my current formula:
Coalesce([First Name]+" "+[Nickname]+" "+[Last Name],[Email])

I cant figure out how to write the formula to accept the null value.  thanks
0
Comment
Question by:Knightsman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 4

Assisted Solution

by:bfuchs
bfuchs earned 500 total points
ID: 41800852
try this
=iif(len([First Name]&[Nickname] & [Last Name])>0,[First Name]&" "&[Nickname]&" "&[Last Name],[Email])
1
 
LVL 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 500 total points
ID: 41800906
or you can try this:
=IIF(  trim( [First Name]+" "+[Nickname]+" "+[Last Name] ) = "" , [Email] , trim( [First Name]+" "+[Nickname]+" "+[Last Name] )  )

Open in new window

1
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41801067
It is much simpler. Use "&" to concatenate Null values with strings:

    Coalesce([First Name] & (" " + [Nickname]) & " " & [Last Name],[Email])

Note that (" " + [Nickname]) will kill the space if [Nickname] is Null.

/gustav
1
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 41801317
to expand on bfuchs and Gustav's posts.

When concatenating string value you get the following behavior:

A & B  =>  AB
A & NULL => A
A + B  => AB
A + NULL => NULL

So I generally use a combination when combining names, for example:

[LastName] & (", " + [FirstName]) & ("(" + [NickName] + ")")

This will not display the comma between last and first name if [FirstName] is NULL.  It also will not display the parentheses surrounding the [NickName] if that value is NULL

HTH
Dale
1
 
LVL 7

Author Comment

by:Knightsman
ID: 41801588
null value aside for a second
Seems when i use the "&" I get the following error:

Works (except for null value of course)
Coalesce([First Name]+(" "+[Nickname])+" "+[Last Name],[Email])

Doesn't work
Coalesce([First Name] &( " " & [Nickname]) & " " & [Last Name],[Email])

Capture.JPG
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41801603
I guess your expression should read:

    [First Name] & (" " + [Nickname]) & " " & [Last Name] & ("@" + [Email])

/gustav
0
 
LVL 7

Author Comment

by:Knightsman
ID: 41801633
On that I get, "contains the wrong number of arguments"
We can actually drop email completely, I don't need it, I must have reverted back to that formula once running into this issue.

I tried bfuchs and I get the same issue with the "&"
@Ryan Chong I get "you may have entered a comma without a preceding value or identifier" which i tried to change the spacing and no luck.
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41801665
Then it should be:

    =[First Name] & (" " + [Nickname]) & " " & [Last Name]

But why don't you create a query for this? These calculated fields is a dead end.

/gustav
0
 
LVL 7

Author Comment

by:Knightsman
ID: 41801695
It must be an Access App restriction.  Im using this as an Access App in SharePoint.  It does not allow me to use an "&" at all.


I'm not sure.  Would it help in this scenario?
I have a form setup that when I order a part, I have the drop down reference this cell so i can select the users first and last name.
0
 
LVL 7

Accepted Solution

by:
Knightsman earned 0 total points
ID: 41801707
HA! This worked fine Concat([First Name]," ",[Nickname]," ",[Last Name])
I orginally read that the concatenate version (excel) for access was Coalesce.  Concat did the job.  

Still dont understand why Access has so many restrictions set for the Sharepoint Access App version.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41801723
Im using this as an Access App in SharePoint
Oh. Missed that completely.

Yes, these are very limited. Has, in fact, very little to do with Access.

/gustav
0
 
LVL 7

Author Comment

by:Knightsman
ID: 41801745
Can you expand on what you mean by it has very little to do with access?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41801775
You've just demonstrated it ...

/gustav
0
 
LVL 7

Author Closing Comment

by:Knightsman
ID: 41814574
found a formula that worked.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question