Help with access query expressions

[kevinh2320]

I have a column in one of my Access queries called CustomerName_DBAName. Below is an example of what that data typically looks like.

CustomerName_DBAName
JOHN T. PUBLIC
JAKE A. SMITH JR.
ABC COMPANY
STEVE JONES - DBA: SUPER STORE
BIG COMMUNICATIONS, LLC
SOME BUSINESS INC.
JOE SLOW - DBA: QUICK DELIVERY

I've added two new columns to my query called CustName and Business name. I need to help with expressions to separate the CustomerName part of the CustomerName_DBAName and the DBAName part of the from the DBAName part of CustomerName_DBAName column and put that data in their respective new columns.

I tried this expression "CustName: Left([CustomerName_DBAName],InStr(1,[CustomerName_DBAName],"-")-1)" for the CustName column. It returns the customer name for those entries that contain a hyphen. But, I only get a "#func!" error for those without a hyphen.

Any help would be greatly appreciated.

The end result should look like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CustomerName_DBAName[/TD]
[TD]CustName[/TD]
[TD]BusinessName[/TD]
[/TR]
[TR]
[TD]JOHN T. PUBLIC[/TD]
[TD]JOHN T. PUBLIC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAKE A. SMITH JR.[/TD]
[TD]JAKE A. SMITH JR.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC COMPANY[/TD]
[TD]ABC COMPANY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STEVE JONES - DBA: SUPER STORE[/TD]
[TD]STEVE JONES[/TD]
[TD]SUPER STORE[/TD]
[/TR]
[TR]
[TD]BIG COMMUNICATIONS, LLC[/TD]
[TD]BIG COMMUNICATIONS, LLC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SOME BUSINESS INC.[/TD]
[TD]SOME BUSINESS INC.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOE SLOW - DBA: QUICK DELIVERY[/TD]
[TD]JOE SLOW[/TD]
[TD]QUICK DELIVERY[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Splitting on the hyphen should not be a problem - we can help you with that.
But before we go down that road, what about entries like "ABC COMPANY"?
I am assuming that should be under BusinessName and not CustName.

So, for entries without hyphens, how do you propose identifying whether the entry is a CustName or a BusinessName?
That logic needs to be clearly defined before we can attempt to program it.

 

[kevinh2320]

Unfortunately the data I'm working with is pretty sloppy. A business name in front of a hyphen may not have any identifier to work with. Example Inc., Co., or something like that. The one thing that is consistent is the "- DBA: part of the string. Given that I think it would work best that if the "- DBA:" is not present then that entry would go into the CustName column. If the "- DBA:" is present then the part to the left would go in the CustName column and the part to the right would go in the BusinessName column.

 

[Joe4]

OK, try this:

CustName: IIf(InStr([CustomerName_DBAName],"- DBA:")>0,Trim(Left([CustomerName_DBAName],InStr([CustomerName_DBAName],"- DBA:")-1)),[CustomerName_DBAName])

BusinessName: IIf(InStr([CustomerName_DBAName],"- DBA:")>0,Trim(Mid([CustomerName_DBAName],InStr([CustomerName_DBAName],"- DBA:")+6)),"")

 

[kevinh2320]

This worked perfectly! Thank you very much!

 

[Joe4]

You are welcome.