FullName and CompanyName in One Field - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

FullName and CompanyName in One Field

Tim Elbert

8 months ago

I would like to set up a field in a Query that gives the Company name, unless there isn't one associated, in which it will then put in the full name. I am using this:
NameOrComp: IIf(([FullNameQ.CompanyName]=Null),[FullNameQ.FullName],[FullnameQ.CompanyName])

I am not doing something right. I get the company name but not the full name in that field.

	Debbie Heaney @Reply 8 months ago
I think you need to use string concatenation. IIf(([FullNameQ.CompanyName]=Null),[FullNameQ.FullName],[FullnameQ.CompanyName]) would become IIf(([FullNameQ.CompanyName]=Null),[FullNameQ.FullName] & ". " &[FullnameQ.CompanyName]) the ". " bit can be changed, or even removed, to suit your needs.

	Richard Rost @Reply 8 months ago
Use IsNull. `IIF(IsNull(CompanyName), FullName, CompanyName)`

	Richard Rost @Reply 8 months ago
Debbie you're close but the IIF function requires three parameters, you've only got two. There, you're just concatenating full name and company name together if company name is null, but what to do if it's not null? You need that third argument.

	Richard Rost @Reply 8 months ago
It's hard to remember when to use the IsNull function or when to check to see if something Is Null, but you almost never check to see if something equals null because nothing equals null. Null doesn't even equal null.

	Richard Rost @Reply 8 months ago
You could also use the Nz function here: `S = Nz(CompanyName, FullName)` This basically says "if CompanyName Is Null then set S equal to FullName, otherwise set it equal to CompanyName."

	Tim Elbert OP @Reply 8 months ago
Thank you all. I will have to research the Nz function and learn more.

	Richard Rost @Reply 8 months ago
The first solution might be easier to understand at this stage. But that's why you're here: to learn! :) And you made it into this Friday's Quick Queries video.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View