Update query to change data in a table

arnel_gp
Steel Contributor
Apr 02, 2022
this will Never happen:

tblHorseInfo.HorseName)<>""

when you left the field blank (or fill it with just spaces, access remove them), access will put Null to it.

you can either use:

Where Not (tblHorseInf.HorseName Is Null)

or

Where Len((tblHorseInf.HorseName & "") <> 0
- turniporangementions
  Copper Contributor
  Apr 03, 2022
  Thanks for the help, I am getting a syntax error on "="
  UPDATE [tblInvoice].[HorseName] = [tblHorseInfo].[Horsename];
  SET WHERE (((tblHorseInfo.HorseName)<>""));
  Regards Bob
  - George_Hepworth
    Silver Contributor
    Apr 03, 2022
    turniporangementions
    
    You haven't yet changed this to the version I suggested.
    The WHERE clause is redundant, as Arnel pointed out.
    
    Here's my original:
    
    UPDATE TableB
    INNER JOIN TableA ON TableB.HorseID = TableA.HorseID
    SET [TableB].[HorseName] = [TableA].[Horsename];
    
    Here's the invalid version:
    
    UPDATE [tblInvoice].[HorseName] = [tblHorseInfo].[Horsename];
    SET WHERE (((tblHorseInfo.HorseName)<>""));
    
    If you change the table names appropriately, it will be:
    
    UPDATE tblInvoice
    INNER JOIN tblHorseInfo
    ON tblInvoice.HorseID = tblHorseInfo.HorseID
    SET [tblInvoice].[HorseName] = [tblHorseInfo].[Horsename];
    
    It is important not just to copy the SQL, but to make sure you understand the logic involved.
    The pattern is
    Update TABLEB
    INNER JOIN TABLEA
    ON TABLEB.KeyField = TABLEA.KeyField
    SET TABLEB.FieldToUpdate = TABLEA.FieldToUpdateFROM
George_Hepworth
Silver Contributor
Apr 02, 2022
turniporangementions

"SET [TableB].[HorseName] = [TableA].[Horsename];"

See the difference?

"SET WHERE (((tblHorseInfo.HorseName)<>""));"

Forum Discussion