Results 1 to 6 of 6

Thread: Help with access query expressions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with access query expressions

    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:

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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Help with access query expressions

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with access query expressions

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Help with access query expressions

    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)),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    May 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with access query expressions

    This worked perfectly! Thank you very much!

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Help with access query expressions

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •