• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34
  • Last Modified:

Access App Coalesce will not populate on null value.

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
Knightsman
Asked:
Knightsman
7 Solutions
 
bfuchsCommented:
try this
=iif(len([First Name]&[Nickname] & [Last Name])>0,[First Name]&" "&[Nickname]&" "&[Last Name],[Email])
1
 
Ryan ChongCommented:
or you can try this:
=IIF(  trim( [First Name]+" "+[Nickname]+" "+[Last Name] ) = "" , [Email] , trim( [First Name]+" "+[Nickname]+" "+[Last Name] )  )

Open in new window

1
 
Gustav BrockCIOCommented:
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
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!

 
Dale FyeCommented:
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
 
KnightsmanAuthor Commented:
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
 
Gustav BrockCIOCommented:
I guess your expression should read:

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

/gustav
0
 
KnightsmanAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
KnightsmanAuthor Commented:
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
 
KnightsmanAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
KnightsmanAuthor Commented:
Can you expand on what you mean by it has very little to do with access?
0
 
Gustav BrockCIOCommented:
You've just demonstrated it ...

/gustav
0
 
KnightsmanAuthor Commented:
found a formula that worked.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now