Solved

Access App Coalesce will not populate on null value.

Posted on 2016-09-15
14
21 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
14 Comments
 
LVL 3

Assisted Solution

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

Assisted Solution

by:Ryan Chong
Ryan Chong earned 125 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 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 49

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 49

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now