Knightsman
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Im using this as an Access App in SharePointOh. Missed that completely.
Yes, these are very limited. Has, in fact, very little to do with Access.
/gustav
ASKER
Can you expand on what you mean by it has very little to do with access?
You've just demonstrated it ...
/gustav
/gustav
ASKER
found a formula that worked.
ASKER
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])