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_Cov = [Modified].[Contact_Neg_Cov];

thank you

error:
Error_memofield
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Is the Excel file linked or imported?  Your query won't be updateable if it includes a linked spreadsheet.

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.
pdvsaProject financeAuthor Commented:
Hi Pat, it is imported.  Yes, Import.Excel.Description is a memo field.  I could still match if only 255 characters.
pdvsaProject financeAuthor Commented:
I just dopnt know where to place the "Like"
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
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?
pdvsaProject financeAuthor Commented:
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.
pdvsaProject financeAuthor Commented:
There are 1000's of records.
PatHartmanCommented:
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.
pdvsaProject financeAuthor Commented:
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?
Gustav BrockCIOCommented:
You may have luck with:

UPDATE
    Import_Excel
INNER JOIN
    Modified
    ON Left(Import_Excel.Description, 255) = Left(Modified.Deliverable, 255)
SET
    Import_Excel.Contact_Neg_Cov = [Modified].[Contact_Neg_Cov];

The query GUI will probably complain that it cannot display this, so you are left with the SQL view.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
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.
Gustav BrockCIOCommented:
You are welcome! Glad it worked for you.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.