Append query

[angie]

Hi, I am trying to append a memo field in a table to another field in a new table. The field that I want to append has three lines (name, address, city) I would like to append the first line (name) to the new table field "name" the second line to the new table field "address" and the third line to the field name "city" . Thank you. Angie

[casinc815]

The QBE should handle your task straightaway. Create a query, choose your table, select the fields in select and test
that you get your information. Then click append and associate the fields.

If there is more to it then you would have to let us know.

[NTC]

when you look at that memo field in a query - that information I don't think is on 3 lines - it is just a string of info though there may be empty spaces between them.... i.e name address city

one can enter a line break inside a memo control that is visually useful to the human when viewing that data in a form/report - but I don't think that line break can be used in a query at all....

so you have the challenge of string manipulation to find / segregate the name from the address and from the city - that will work thru out your table. For example if there was always a comma between them you could count to the first comma - and then take everything to the left of it as the Name. This string manipulation is generically the InStr methods which you are going to want to do a little research to under stand better. There is Left, Right, Mid and LEN that will be of particular help to you.

[angie]

Hi Ntc. I have been reading about instr but still having problems. I used this code
mid([company],instr([company],"#")+1,6)
it brings up the first 6 letters. I need it to stop where the return is ( when I entered the info into the text field I typed it using three lines using the shift return to force a new line, I did this years ago before I understood access). Any help thanks

[lfpm062010]

If there is 3 lines used in memo field, you can use chr(13) & chr(10) to seperate the lines.

Chr(13) is carriage return and Char(10) is line feed.

So, this is how it would be done in VBA.
endLine1 = InStr(1, MEMOFIELD, Chr(10))
tmpLine1 = Mid(MEMOFIELD, 1, endLine1 - 2)
MsgBox "LINE #1 = (" & tmpLine1 & ")" endLine2 = InStr(endLine1 + 1, MEMOFIELD, Chr(10))
tmpLine2 = Mid(MEMOFIELD, endLine1 + 1, endLine2 - endLine1 - 2)
MsgBox "LINE #2 = (" & tmpLine2 & ")"
endLine3 = Len(MEMOFIELD)
tmpLine3 = Mid(MEMOFIELD, endLine2 + 1, endLine3 - endLine2)
MsgBox "LINE #2 = (" & tmpLine3 & ")"

This is how you would do it in a query.

SELECT [MEMOFIELD], Mid([MEMOFIELD],1,InStr(1,[MEMOFIELD],Chr(10))-2) AS LINE1, Mid(Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+1),1,InStr(1,Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+1),Chr(10))-2) AS LINE2, Mid(Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+InStr(1,Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+1),Chr(10))+1),1,Len(Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+InStr(1,Mid([MEMOFIELD],InStr(1,[MEMOFIELD],Chr(10))+1),Chr(10))+1))) AS LINE3
FROM [TABLENAME];

[angie]

This is not a memo field its a text field that I have entered address into the first line would be like this
angie Anderson (new line by using ctrl/enter)
5456 castle dr (new line by using ctrl/enter)
Tallahassee fl 32540 (new line by using ctrl/enter)
now I would like to separate each line in a query.
Thanks Angie

[lfpm062010]

It should still work. As long as there are 3 lines seperate by new lines (ctrl-enter).

[angie]

Hi, I have this much working so far.
exp2:left([company],Instr([company],chr(10))) (this will return the first line)
exp1:mid([company],Instr([company],chr(10))+1) ( this returns the second and third line)
I need the exp1 to show only the second line and a new exp to show only the third line.

This is in a query
Thank you Angie

[angie]

Hi do you want me write it just like you have or do I need to replace memofield with company.

[lfpm062010]

You need to replace the [MEMOFIELD] with [Company] if that is where you have the 3 lines that you are trying to convert.

You also need to replace [TABLENAME] with the table that contain the filed [Company].

[angie]

Idid the following and got the following error: “the syntax of the subquery in this expression is incorrect”
I triedthis as the criteria and also as a new field (expression) in the query got sameresults.


select[owner],mid[owner],1,instr(1,[owner],chr(10))-2)asline 1,mid(mid([owner],instr(1,[owner],chr(10))+1),1,1,instr(1,mid[owner],instr(1,[owner],chr(10))+1),chr(10))-2)asline 2,mid(mid([owner],instr(1,[owner],chr(10))+instr(1,mid([owner],instr(1,[owner],chr(10))+1),chr(10))+1),1,len(mid[owner],instr(1,[owner],chr(10))+instr(1,mid([owner],instr(1,[owner],chr(10))+1),chr(10))+1)))asline 3 from [owner]

Thankyou Angie

[lfpm062010]

Is your table name called "owner"? and does it have a column field called "Company"?

If the above statement is true, then use the following syntax. If not, please either attached you db or describe your table and columns.

SELECT owner.Company, Mid(owner.Company,1,InStr(1,owner.Company,Chr(10))-2) AS LINE1, Mid(Mid(owner.Company,InStr(1,owner.Company,Chr(10 ))+1),1,InStr(1,Mid(owner.Company,InStr(1,owner.Co mpany,Chr(10))+1),Chr(10))-2) AS LINE2, Mid(Mid(owner.Company,InStr(1,owner.Company,Chr(10 ))+InStr(1,Mid(owner.Company,InStr(1,owner.Company ,Chr(10))+1),Chr(10))+1),1,Len(Mid(owner.Company,I nStr(1,owner.Company,Chr(10))+InStr(1,Mid(owner.Co mpany,InStr(1,owner.Company,Chr(10))+1),Chr(10))+1 ))) AS LINE3
FROM owner;