Blank Last Name Field to Display as Company

[admsvcs]

MS Access 2010

I have a report in which some records are companies where as others are individuals (First Name, Last Name). For a record in a report where there is no Last Name, I would like the Company to display.

Please also tell me where the code is to be inserted, for example, the Properties Sheet>>Data tab>>Control Source...if that is where it goes. Thank you for your help!

 

[GinaWhipp]

I would create a seperate query...

SELECT tblCustomerProfile.cpCustomerID, IIf([LastName]= "" Or IsNull([LastName]), [cpCompanyName], [cpLastName] & ", " & [cpFirstName]) AS Client
FROM tblCustomerProfile
ORDER BY tblCustomerProfile.cpCustomerID;

Then use that query in the query that is the RecordSource of the Report to get the names to show. Of course, change the names to match your tables and fields.

 

[admsvcs]

Appreciate your reply, Gina, but I have no idea what to do with the code. I created a query using the query wizard including all of the fields from the underlying table. Beyond that, I do not know what to do. Where does the code go? Thank you, Norm

 

[plog]

In design view of your report, right click on the textbox that you want the person's name/company to appear, click 'Properties' on the menu, click on the 'Data' tab of the dialog that pops up and put this in the Control Source field:

=IIf(isnull([LastName]), [CompanyName], [FirstName] & " " & [LastName])

Change all the data between brackets [] to the names of the corresponding fields in your underlying data source.

 

[GinaWhipp]

If you are still unable to get the line in place you can post the SQL of your query here I can retype it and you can copy paste it back.

 

[admsvcs]

Thank you, plog! I have attache two screenshots for you. I think I have entered the code correctly, but I am getting an error of "#type!" in the report. I have been unable to nail down the difficulty. I am only guessing here, but maybe the error is indicating a mismatch in data types, but I don't see how that could be. Your thoughts, please. Norm

 

[boblarson]

Sorry to contradict Plog, but I would NOT put it on the report directly. Create it in the query like Gina said. But to make it easier, open the query in design view and then in the QBE Grid type in this for the part that says FIELD

Client:IIf(Len([Last Name] & "") = 0, Nz([Company],""), [Last Name] & ", " & [First Name])


And the reason why I say do it on the query instead of in the report's text box is that it is faster and more efficient and you do want to avoid things (if it is possible, which in this case it is) that slow down reporting.


EDIT: Added screenshot to be more helpful

 

[admsvcs]

Bob, your suggestion work perfectly. Thank you! Now I have to spend a little more time with some unfamiliar nomenclature, such as Len and NZ. The learning curve is straight up!!!! Hopefully, I will be able to help others in due time.

I wish I was up in your neck of the woods. I am from Bellingham, WA, but moved to TX, near San Antonio, about six years ago. We normally return to the PNW for the summers, but this year we are stuck in 100 plus heat. Ugh! I will happily send some sunshine your way if you will send some rain...lots of it!!! Stay well and thank you again! Norm

 

[boblarson]

And just an FYI for you.

Len is the function to determine length. So, if you have a field which is null, you can't check its length directly you will get nothing. But you can append the empty string which will then allow you to check the length (it will show up as zero if either null or empty) and that shortens up things. Some people use the NZ function (Null to Zero) which will let you substitute values for nulls. So you could use

IIf(Nz([Last Name], "") = "", [company], [Last Name] & ", " & [First Name])

And that is okay too but from some conversations with some pretty knowledeable people, it is more efficient to check the length than to use the NZ to substitute and then do the comparison with the empty string.

So hopefully that helps.