Forum Discussion
Update query to change data in a table
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
UPDATE [tblInvoice].[HorseName] = [tblHorseInfo].[Horsename];
SET WHERE (((tblHorseInfo.HorseName)<>""));
Regards Bob
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
- Think I got it right 🙂
UPDATE tblInvoice INNER JOIN tblHorseInfo ON tblInvoice.HorseID = tblHorseInfo.HorseID SET tblInvoice.HorseName = [tblHorseInfo].[Horsename];- Congratulations on solving the problem.
Try it on a backup copy of the data first!
