Results 1 to 5 of 5
  1. #1
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232

    Left([Bill To],InStr([Bill To],Chr(10)))

    I use the following code to pull just the first line of a three line field in a query. Left([Bill To],InStr([Bill To],Chr(10)))


    this works great , but I have a few accounts that only have one line and when I run this query it will leave all the customers with one line in the field blank and if there is three lines than it works great by just showing the first line. I need it to show the first line if there is three lines but also if there is just one line. Thank you Angie

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I'm guessing that the reason 20+ members have looked at your thread, without venturing an answer, is that like myself, they have no idea what you're talking about when you mention a 'three line field in a query.'

    Perhaps if you could explain, in plain language, what your situation is, we could help.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    Quote Originally Posted by Missinglinq View Post
    I'm guessing that the reason 20+ members have looked at your thread, without venturing an answer, is that like myself, they have no idea what you're talking about when you mention a 'three line field in a query.'

    Perhaps if you could explain, in plain language, what your situation is, we could help.

    Linq ;0)>

    Bill To Field has a Row Source Type “Bill To: [Contractors Dealers]![Company] & Chr(13) & Chr(10) & [Contractors Dealers]![Address] & Chr(13) & Chr(10) & [Contractors Dealers]![City] & " " & [Contractors Dealers]![State] & " " & [Contractors Dealers]![ZIP]

    And a Default Value “=IIf(Not IsNull([Forms]![Customer]![CompID]),[Forms]![Customer]![Owner Address],[Forms]![customer]![company] & [Forms]![customer]![firstname] & " " & [Forms]![customer]![lastname] & Chr(13) & Chr(10) & [Forms]![customer]![street number] & " " & [Forms]![customer]![address] & Chr(13) & Chr(10) & [Forms]![customer]![City] & " " & [Forms]![customer]![state] & " " & [Forms]![customer]![zip])

    This allows me to use either the default value or choose a value using the row source type.
    The problem is in the query I have created to transfer data into quickbooks I have a field like this Account Name: Left([Bill To],InStr([Bill To],Chr(10)))

    This works great but if there is no address, city, state, or zip in the row source type than it leaves the query field account name blank.
    I hope this explains it a little better.

  4. #4
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Apr 2015
    Posts
    128
    You need to use the IIf() function here. Use it to test if the InStr() function returns a non-zero (indicating Chr(10) is found) or not. And if not, just return [Bill To] as is. Try this:

    IIf(InStr([Bill To],Chr(10)) <> 0, Left([Bill To], InStr([Bill To],Chr(10))), [Bill To])

  5. #5
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    Thanks Keviny04. That works great. Sorry it took so long to try but just got back from business trip. Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-27-2013, 11:15 PM
  2. How to calculate a bill
    By Diamond in forum Queries
    Replies: 1
    Last Post: 03-21-2013, 07:17 AM
  3. Replies: 5
    Last Post: 03-18-2013, 12:31 PM
  4. Parsing a Bill of Materials
    By Pat in forum Programming
    Replies: 3
    Last Post: 05-04-2012, 04:09 PM
  5. Bill of Materials Code Questions
    By SALPBE in forum Programming
    Replies: 4
    Last Post: 01-31-2011, 04:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums