Problem updating fields from one table to another, but the data does not match

[matt1000]

Hello,



My problem is this, I am attempting to update multiple fields from one table to another. However, the data within the fields doesn't match exactly in the field which I am joining the tables on.

An example:

tbl1.Invoice_No = 12345-01
or
tbl1.Invoice_No = ABC9876

tbl2.Invoice_No = 12345
or
tbl2.Invoice_No = 9876

Attempted Variations of the following SQL Statement:

UPDATE tblAnalysis_file SET tblAnalysis_file.completed = "Complete", tblAnalysis_file.chart = "Disregard", tblAnalysis_file.status = "On AP Invoice Table", tblAnalysis_file.comments = "Approved But Previously Failed "
WHERE (((tblAnalysis_file.Invoice_No) LIKE (tblAp_Invoice.Invoice_No)));


Where the problem lies for me, besides this not obviously working, is how to get Access to, in a way execute a trim function. Some "Invoice_No" 's will have leading, or lagging, extra information in one table, but not in the other table.

Any help is gratefully appreciated in advance!

Regards,

Matt r.

[Rixxe]

You will probably need to use VBA code, but you can just use Trim as an Expr if you really want.

Expr1: Trim([Fieldname])

Or


This might be useful to you:

 
Dim MyString, TrimString

MyString = "  <-Trim->  "    ' Initialize string.

TrimString = LTrim(MyString) 
' TrimString = "<-Trim->  ".

TrimString = RTrim(MyString)
' TrimString = "  <-Trim->".

TrimString = LTrim(RTrim(MyString)) 
' TrimString = "<-Trim->".

' Using the Trim function alone
' achieves the same result.
TrimString = Trim(MyString) 
' TrimString = "<-Trim->".

Good luck