Solved

Access App Coalesce will not populate on null value.

Posted on 2016-09-15
14
22 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 32
Sum Multiple Columns in Access Query 5 53
Running sum query 6 35
SQL profiler equivalent in MS-Access 3 46
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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