Solved

Access App Coalesce will not populate on null value.

Posted on 2016-09-15
14
29 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 125 total points
ID: 41800852
try this
=iif(len([First Name]&[Nickname] & [Last Name])>0,[First Name]&" "&[Nickname]&" "&[Last Name],[Email])
1
 
LVL 52

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 50

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 48

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 50

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
 
LVL 50

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 50

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 50

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

Industry Leaders: 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

696 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