none
VBA procedure to search a string in two Access tables RRS feed

  • Question

  • Hi, everyone! 

    I wrote a  procedure as Union query in MS ACCESS 2007 to search a name of a person in two Access tables and display the result into a new table, Suspendati1.

    It worked, but I want to improve it, by updating the field Field55 (from Suspendati1) with the file name where the searched person's name is found. When I added the UPDATE statement, it doesn't work at all.

    The error message is "Microsoft database engine could not find the object 

    ... INSTR(MCEB.NUME,[de cautat].[nume cautat]) > 0 
    UPDATE Suspendati1
    SET FIELD55='MCEB' WHERE  ISNULL(FIELD55). Make sure that object exists and spell its name and the path correctly." 

    Maybe an Union query doesn't support other statements, like UPDATE ? The full code is below. Please give me a suggestion! Thank you! Note: the separator ";" between the statements doesn't solve the problem. In this case, the error message is "Character found after SQL statement".

    TABLE [Suspendati1] UNION ALL
    SELECT MCEB.COD, MCEB.NUME, MCEB.PRENUME, MCEB.VARSTA, MCEB.SECT, MCEB.LOCALITATE, MCEB.FIELD55
    FROM [DE CAUTAT], MCEB
    WHERE INSTR(MCEB.NUME,[de cautat].[nume cautat]) > 0 
    UPDATE Suspendati1
    SET FIELD55='MCEB' WHERE  ISNULL(FIELD55) 
    UNION ALL SELECT Suspendati.COD, Suspendati.NUME, Suspendati.PRENUME, Suspendati.VARSTA, Suspendati.SECT, Suspendati.LOCALITATE, Suspendati.FIELD55
    FROM Suspendati
    WHERE  INSTR(Suspendati.nume,[de cautat].[nume cautat]) > 0 
    UPDATE Suspendati1
    SET FIELD55='Suspendati' WHERE ISNULL(FIELD55);

    Sunday, September 16, 2018 6:34 AM

All replies

  • You need to understand - in programming something have sense only if it satisfied formal rules named language grammar. You need to study about Union and Update.

    Sincerely, Highly skilled coding monkey.

    Sunday, September 16, 2018 8:05 AM
  • UNION queries do not support UPDATE nor can they be updated in any way.  You have to update the underling tables within the UNION query.

    Gina Whipp Microsoft Access MVP 2010-2015 Access Diva Tips: https://www.access-diva.com/tips.html

    Friday, September 21, 2018 5:48 AM
  • Also, I must update the tables without Union query. Thank you!
    Friday, September 21, 2018 6:06 AM