Left / Mid/ Right for a query

[angie]

I have a text field that has three lines in the field. I would like to separate the lines in a query I tired the Mid (see below ) which leaves off the first line but the third lines still shows. But I will need to do the same for the third line also.
Thanks Angie


Address: Mid([bill to],InStr([bill to],Chr(10)))

[orange]

Show us all the text in the field.
And show us how you'd like it broken out.

[John_G]

If you know you always have three lines separated by chr(10), use the split() function:

Address: split([bill to], Chr(10))(1)

Split() returns an array, with the references to the array members being zero-based, to the above example gives you the second line of the [bill to] field.

[angie]

John White
5933 Castle Dr
Pace Fl 32570

I would like in the query to separate into three different fields for a report.
I would have like Name = first line Address = Second Line and City = Third Line

[orange]

Along the lines of what John suggested, you could try the following logic (assuming BillTo is the name of your field)

Dim myData as Variant
Dim i as integer
MyData = Split(BillTo,vbCrLf)
for i = lBound(MyData) to Ubound(MyData)
      select case i
      case 1
         [Name] = MyData(i)
    Case 2
        [Address] = MyData(i)
    Case 3
     [City] = MyData(i)
     case else 
      MsgBox "unexpected data in the input"
   end select
next i

[angie]

This is for a query where do I put the above code?

[ssanfu]

Another option...

In a query, add these columns:

Name: Left([bill to],InStr([bill to],Chr(10))-1)
Address: Mid([bill to],InStr([bill to],Chr(10))+1,InStr(InStr([bill to],Chr(10))+1,[bill to],Chr(10))-InStr([bill to],Chr(10)))
City: Mid([bill to],InStrRev([bill to],Chr(10))+1)

I would advise you NOT to use spaces in object names.
Instead of
"[bill to]" (with a space), use
"BillTo" or "Bill_To".

[orange]

Name is a reserved word in Access

so, in the query try

sName: split(BillTo,chr(13) & chr(10) ) (0)
Address:split(BillTo,chr(13) & chr(10) ) (1)
City: split(BillTo,chr(13) & chr(10) ) (2)

[angie]

Thanks Ssanfu, the above code in a query worked great.

Angie

[ssanfu]

You're welcome.....