pdvsa
asked on
Update Query and Like (memo field)
Experts,
I can not make an update query where the fields are memo.
Is there a work around?
I can not update on an ID as there is not one so I must update based on text.
UPDATE Import_Excel INNER JOIN Modified ON Import_Excel.Description = Modified.Deliverable SET Import_Excel.Contact_Neg_C ov = [Modified].[Contact_Neg_Co v];
thank you
error:
I can not make an update query where the fields are memo.
Is there a work around?
I can not update on an ID as there is not one so I must update based on text.
UPDATE Import_Excel INNER JOIN Modified ON Import_Excel.Description = Modified.Deliverable SET Import_Excel.Contact_Neg_C
thank you
error:
ASKER
Hi Pat, it is imported. Yes, Import.Excel.Description is a memo field. I could still match if only 255 characters.
ASKER
I just dopnt know where to place the "Like"
Since you can't join on memo fields, you'll have to find some other way to match the two tables. Don't they have primary keys?
ASKER
Yes, there are ID's however the db that I have imported the records into do not have a match on every record and this is why I need to match on the memo field.
ASKER
There are 1000's of records.
The only way to use one table to update another is to have a common field. You don't seem to have one.
You can try temporarily adding a short text field to each table and populating it with the first 255 characters of the memo field. Try to apply a unique index to the new short text field. If you can, then you can safely use it for joining the tables and updating. If you can't, you may have to come up with a code solution but I would need to know a lot more about what is in each table and what you are trying to accomplish before embarking on that path.
You can try temporarily adding a short text field to each table and populating it with the first 255 characters of the memo field. Try to apply a unique index to the new short text field. If you can, then you can safely use it for joining the tables and updating. If you can't, you may have to come up with a code solution but I would need to know a lot more about what is in each table and what you are trying to accomplish before embarking on that path.
ASKER
Could I also possibly
-make a copy of the db.
-Change the property from memo to text.
-Update now that I have a text field and not memo
-then update the orig db based on the ID from the copy db?
-make a copy of the db.
-Change the property from memo to text.
-Update now that I have a text field and not memo
-then update the orig db based on the ID from the copy db?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav, sorry for my late reply. I got sidetracked. Thank you for the response. It worked perfectly. It did display the error in the GUI like you mentioned but still executed.
You are welcome! Glad it worked for you.
/gustav
/gustav
Are you trying to join on memo fields? I believe that only the first 255 characters are used for searching and I don't know if you can join on them.