Extract several strings from Memo field 1

[LakotaMan]

Hi All,
Am using ver 2007, I have a memo field with several lines, separated by returns, here is an example of this field data:

1 ) EXTENT OF VAPOR LEAKAGE OUT FRESH AIR DAMPER?
2 ) COLOR OF WATER IN SEAL? (LIGHT BROWN IS NORMAL)
3 ) SUPPLEMENTAL AIR FAN MECHANICALLY SOUND?
4 ) FD FAN MECHANICALY SOUND?

As you can see, not all lines end in a “?” I need to extract each line separately.
Can this be done using 1 query? What extraction function would work best? (Mid, Left, Right) and how would I use it?
Any help you can give me on this will be greatly appreciated.
LM

 

[MajP]

you can use the split function as demonstrated below. But I am not sure how you want to show your data, or what you want to do once you return the items.

Public Function getMemoItems(varMemo As String) As String()
    getMemoItems = Split(varMemo, vbCrLf)
End Function

Public Sub test()
  Dim mymemo As String
  Dim aMemo() As String
  Dim memItm As Variant
  mymemo = DLookup("fldMemo", "table1")
  aMemo = getMemoItems(mymemo)
  For Each memItm In aMemo
    Debug.Print memItm
  Next memItm
End Sub

You can use the split function directly in a query. If you want the first item from the memo field then it would be something like

select split([memoFld])(0) as FirstLine, split ([memoFld])(1) as SecondLine.

If it was me I would normalize the data using this function.
1. return a recordset from the table.
2) loop the recordset
3) get the Primary key, and return the array using getMemoItems.
4) loop the array and do an insert query on new child table inserting the PK as a foreign key and each line of text.

 

[LakotaMan]

MajP --Thanks so much for your help, this does exactly what I'm looking for.
I am in the process of re-designing this DB and needed to get this info out seperately to do that.
have a great day!
LM