Memo fields in a query

[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

 

[June7 via AccessMonster.com]

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, set a
string variable to the field contents. Then use it for the comparison. If the
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.

 

[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.

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

Jeff

 

[June7 via AccessMonster.com]

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.

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

[quoted text clipped - 4 lines]

into a one-dimensional array OR use InStr and Mid functions in a custom
function to break it up.

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

Jeff

 

[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.

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

Jeff

 

[June7 via AccessMonster.com]

I did not suggest you use it IN a query but ON a field of a query in code.

Never tried to parse out memo box so did some experimenting. These work:
x = Split(Me.mmoTest, vbCrLf)

[quoted text clipped - 5 lines]

memo field and then in a loop parse each record into array and in another
loop (nested) save the values from array to table.

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

Jeff

 

[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.

 

[June7 via AccessMonster.com]

Yes, that would be same outcome. Whether directly in a query or indirectly
through a function, still trying to use Split function. Split function
generates an array. Can't have an array in a query. The VBA code steps I
suggest are:
1. Select query for the memo and eventID fields
2. Loop through recordset and apply the Split function on memo field
3. Loop (nested in first loop) through the array generated by Split function,
add each name and eventID to new record in the table

 

[June7 via AccessMonster.com]

Edit to previous post: Since you really want to store the MemberID will have
to match name to ID (this assumes no variation in spellings). Can use DLookup
function on the Members table; if you get valid match then save the MemberID
and EventID to record, if not then use the name and deal with later in manual
edit.

 

[Jeff]

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

 

[June7 via AccessMonster.com]

Not what I had imagined but if works! This does something don't remember ever
tried. So can't say if you really need all those fields in SELECT clause or
the concatenation in the DLookup or OutingConvert1 in the FROM clause. If any
of these aren't right might be cause of duplicates, otherwise no idea. You
are going to create a new table from this query? Might use Find Duplicates
query on the result table to help clean up although not sure how to use it to
delete the duplicates, just find them. Congrats on clever solution, even if
not perfect, looks like it will get you to goal.

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