update query using left function

  • Thread starter Thread starter carol
  • Start date Start date
C

carol

following query is not updating properly. it appears that
only the exact field matches are updating and that the
left function criteria is completely ignored. can you
help? thx.

UPDATE [contact nelson draft] LEFT JOIN VENDORS ON
[contact nelson draft].vendor = VENDORS.VENDOR SET
[contact nelson draft].[vendor#] = [vendors].[vendor#]
WHERE (((Left([vendors].[vendor],6))=Left([contact nelson
draft].[vendor],6)));
 
following query is not updating properly. it appears that
only the exact field matches are updating and that the
left function criteria is completely ignored. can you
help? thx.

UPDATE [contact nelson draft] LEFT JOIN VENDORS ON
[contact nelson draft].vendor = VENDORS.VENDOR SET
[contact nelson draft].[vendor#] = [vendors].[vendor#]
WHERE (((Left([vendors].[vendor],6))=Left([contact nelson
draft].[vendor],6)));
Click to expand...

Well... yes. Your JOIN clause ensures that [contact nelson
draft].[vendor] must be exactly equal to [vendors].[vendor] before any
criteria are applied. The WHERE condition will always be true, since
the two Vendor fields are already known to be identical.

You can join on the Left() expression but the resulting query will
probably not be updateable, since I suspect that you cannot uniquely
identify a record in [contact nelson draft] using only the first six
bytes!

What are some typical values of [Vendor]? Should this actually be
split into two fields? Apparently the leftmost six bytes have a
different meaning than the remainder, suggesting that the field is not
"atomic", thereby violating first normal form.
 
Back
Top