Trimming Data in Listbox

[ScrmingWhisprs]

Another issue for my contacts database for work. I have a listbox on the edit contacts form that lists all contacts in the database.

The listbox rowsource is

SELECT [ContactID], [LastName] & ", " & [NamePrefix] & " " & [FirstName] & "   " & [Business/Organization] FROM tblContacts ORDER BY [LastName] & ", " & [FirstName] & " " & [Business/Organization];

Some of the contacts that are businesses or organizations, do not actually have the first and last name filled in, just the business/organization name. So what happens in the list I get all of the entries that do not have FirstName/LastName at the top of the list, with the Business name following a few spaces. The way it looks is:

, Stop & Shop Supermarkets

But I would like to trim that beginning part if there is no FirstName/LastName so that Stop & Shop Supermarkets gets sorted with the S's, like this:

Stabile, Lisa
Stop & Shop Supermarkets
Stott, Joan

Is there something I need to put in the rowsource to accomplish this? I've searched these forums for an answer, and turned up no results.

Attached, I have a screenshot of the form with the listbox.

Thanks
ScrmingWhisprs

 

[Matt Greatorex]

If you replace

[LastName] & ", " & [NamePrefix]

with

[LastName] & IIf(isnull([LastName],"",", ") & [NamePrefix]

you should get a zero length string, instead of the comma, whenever there is no value in the LastName field. Playing around with the other commas in the same way should allow you to remove them all, if the relevant fields are blank.

Hope that helps.

 

[ScrmingWhisprs]

I get an error saying:

Syntax error (missing operator) in query expression '[LastName] & IIf(isnull([LastName],"",", ") & [NamePrefix] & " " & [FirstName] & " " & [Business/Organization] FROM tblContacts ORDER BY [LastName] & ", " & [FirstName] & " " & [Business/Organization];


Can you put something like that in a SQL statement? Do I put that in the Rowsource, or somewhere else?

ScrmingWhisprs

 

[Matt Greatorex]

oops, should have been

[LastName] & IIf(isnull([LastName]),"",", ") & [NamePrefix]

The bracket after [LastName] was missing.
Sorry about that (put it down to lack of sleep)

You can use it in a few ways - and I'm sure other forum members will suggest them - but I usually construct it as a SQL statement, then assign it to the report, on opening. If for no other reason than I find it easier to see exactly what's going on that way.

 

[ScrmingWhisprs]

Cool. That worked great!!!

Thank you so much!

ScrmingWhisprs