Memo fields in a query

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I am currently converting a database to a more efficient design for a
client.

The client previously had a memo field where they stored all the names
of people participating in an outing. Now I am using a many-to-many
table design to take care of this more efficiently.

Outing
EventID
Outing List (memo field)

tblAttendees
Event ID
Member ID

MemberList
Member ID
Full Name


Anyway, the issue is that I'm trying to convert these memo fields into
entries in the tblAttendees table . I had though to compare and
extract valid member names from the memo field and then create an
append query to create entries into the tblAttendees table, but
there's an error message saying that I can't compare a Memo field in a
query.

Is there a way to extract the memo fields somehow so that I can do
comparisons or is this just a lost cause?

Thanks

Jeff
 
Are the names in the memo field separated by comma or some other punctuation?
If not, I suppose this is why you need to compare? Don't do the task in the
query. I have never tried to parse a memo field but because of the error
sugguest using a procedure that queries the table for the memo field, seta
string variable to the field contents. Then use it for the comparison. Ifthe
data is delimited, might use the Split function which will parse the data
into a one-dimensional array OR use InStr and Mid functions in a custom
function to break it up.
Click to expand...

No, apparently all they did was hit "enter" after each name. So would
a string variable work in that case?

Jeff
 
Never tried to parse out memo box so did some experimenting. These work:
x = Split(Me.mmoTest, vbCrLf)
Builds a one dimensional array of the values.
y = InStr(Me!tstmemo vbCrLf)
Finds the position of vbCrLf
Didn't even have to use a variable, acted on the box contents and recordset
field value of open form. So in code, build a recordset Select query of the
memo field and then in a loop parse each record into array and in another
loop (nested) save the values from array to table.
Click to expand...

I tried this in a Select query and it said "undefined function Split
in expression"

Jeff
 
Sorry, I should have been more specific.

I set up in VBA in a used-defined function which I then used in an
expression in the query.

And got that message.
 
I tried this, and I'm getting a TON of duplicate records, although it
does appear to be giving the correct records in all of that.

This is my query:

SELECT OutingConvert1a.EventID, OutingConvert1a.EventName,
OutingConvert1a.[Outing List], DLookUp("[Member
ID]","OutingConvert1","'" & [Outing List] & "' LIKE '*'& [Full Name]
&'*'") AS MemberID
FROM OutingConvert1, OutingConvert1a;


Any help would be appreciated!

Jeff
 
Back
Top