Update Query, using Replace

If you want to get down to just the raw numbers without the zeros and dashes, you should nest your Replace statements.

Replace(Replace([import-CSM2].[Reference Number],0,""),"-","")

Select allOpen in new window

The Outer Replace removes the dash and the inner Replace removes the zeros.

Tom

Tom:

Thank you.  

Could you let me know how to modify the below UPDATE?  I am far from any level of basic programming.  The parenth cause me issues.

UPDATE [import-CSM2], tblLetterOfCredit SET [import-CSM2].LCID = [tblLetterOfCredit].[LetterOfCreditID]
WHERE (((Replace(Trim("-" & [Reference Number]),"-",""))=Replace(Trim("-" & [LCNo]),"-",""))) OR (((Replace(Trim("0" & [Reference Number]),"0",""))=Replace(Trim("0" & [LCNo]),"0","")));

pdvsa,
Did you bother to look at the database I posted?

UPDATE tblLetterOfCredit, [import-CSM2] SET tblLetterOfCredit.GuaranteeCode = [import-CSM2].[Guarantee Code]
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([LCNo],"-",""),"0","")));

Select allOpen in new window

Try the above. It works with the single record that you provided, but should be tested on a large data set before claiming victory.
Tom

Tom,

Do you get a datatype mismatch when running?  
I seem to get this error on my end if I copy and paste it.  

Hi Pat,
Yes, I did download.  It might not be best suited for me because I would need to apply to several areas in db and it would take a lot of time for me.  The best solution for me is to modify what I currently have.  Let me know if your solution could be applied to what I have now without adding additional fields to the tables.

No -- I am not getting any error message.  I downloaded the version again and put the code in just to double check.  
Are you working with a larger data set when you get the error message?  If so, I think the error is related to the two fields that we are making adjustments to.  I would recommend create a select query with the two columns with the replace code to see if that produces the error message.

Tom

OK I tried on the original db I uploaded and I do not get the error either.  I imported tblLetterOfCredit with the complete data set and I do get the datatype mismatch error but after I click OK the field says #Name as shown in the pic which leads me to believe there is a naming issue?  


I did create a separate select query with only tblLetterOfCredit but still get the datatype mismatch and #name
SELECT tblLetterOfCredit.GuaranteeCode
FROM tblLetterOfCredit
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([LCNo],"-",""),"0","")));

I also created a select with both import-CSM2 and tblLetterOfCredit but same issue
SELECT tblLetterOfCredit.LCNo, [import-CSM2].[Reference Number]
FROM tblLetterOfCredit, [import-CSM2]
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([LCNo],"-",""),"0","")));

I dont see why I would have this datatype mismatch and #name issue with simply a larger dataset.  

Let me know what is the next step.    

I see that LCNo is being used on both sides of the = sign.  WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([LCNo],"-",""),"0","")));

I dont really understand completely but in my original update qry using TRIM there is [Reference Number] from importCSM-2 and [LCNo] on tblLetterOfCredit on either side.
(Replace(Trim("0" & [Reference Number]),"0",""))=Replace(Trim("0" & [LCNo]),"0","")

Is it possible that the reference number will have a letter?
Tom

Yes, they are both text formatted actually (LCNo and Reference Number)

Can you provide an example of a LCNO and Reference Number that contain a letter?

Here are 2 examples and they reside in both tables:  
CTCS-782784
02102-1378755LDE

Is that how they look in both tables?
I was thinking that the Letter of Credit Table would have the following values:
CTCS-782784
21021378755LDE

Is this correct?

<Is that how they look in both tables?
==>Yes.  

I actually import values from import-CSM2  to tblLetterOfCredit.  The stripping of the characters only happens in the query and doesnt change the integrity of the data.

Let me know if your solution could be applied to what I have now without adding additional fields to the tables

I didn't add additional fields to tables.  I used a query to create the calculated fields so that the tables could be joined.

<Is that how they look in both tables?
==>Yes.  I mean the characters still reside in tblLetterOfCredit and import-CSM2.

I was able to duplicate the error when I added an Import record that had a null for Reference Number.  Does this exists in your data?

Also note, the issue with the where statement that Pat pointed out.

<I was able to duplicate the error when I added an Import record that had a null for Reference Number.  Does this exists in your data?
==>Yes, I do have some Nulls in both tables.  

let me know what is next...

I am going to assume that we will not be able to work with NULL values, so we have the option of removing.   I have added two columns to filter out null values on both tables.

UPDATE tblLetterOfCredit, [import-CSM2] 
SET tblLetterOfCredit.GuaranteeCode = [import-CSM2].[Guarantee Code]
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([Reference Number],"-",""),"0","")) 
AND (([import-CSM2].[Reference Number]) Is Not Null) 
AND ((tblLetterOfCredit.LCNo) Is Not Null));

Select allOpen in new window

wow that looks nice and I didnt get an error.  I think I know what you did.  

I have to add something to it though.

In addition to replacing the "-" and the "0" with ""
I also need to replace " " and "/" with ""
Would this be correct:  
((Replace(Replace([LCNo]," ",""),"/",""))=Replace(Replace([Reference Number]," ",""),"/",""))

here is the SQL with this added but I think there is something wrong because it gives me the datatype mismatch again BUT only if I have it on the OR line and not the AND line.
UPDATE tblLetterOfCredit, [import-CSM2] SET tblLetterOfCredit.GuaranteeCode = [import-CSM2].[Guarantee Code]
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([Reference Number],"-",""),"0","")) AND (([import-CSM2].[Reference Number]) Is Not Null) AND ((tblLetterOfCredit.LCNo) Is Not Null) AND ((Replace(Replace([LCNo]," ",""),"/",""))=Replace(Replace([Reference Number]," ",""),"/","")));

<Is that how they look in both tables?
==>Yes.  I mean the characters still reside in tblLetterOfCredit and import-CSM2.

Yes.  The queries do not change how the data is stored.  They just exclude the characters you wanted to exclude.

that looks super techy. Way outta my league.  I sincerely appreciate your assistance and sticking with me through this.  

I do have a Function but I do not think it works as planned.  I am not using it anylonger.  I might ask a question on how to modify the below at some pt but for now will use the Update query method and no function.

Function replaceNoNum(a As String) As String
'Called by:
'WHERE replaceNoNum([Refence Number])
'Not In (SELECT replaceNoNum([Reference Number]) FROM [import-CSM2])
'seems like can get a data type mismatch error though...8-31-13
'not using it

Dim i As Integer
Dim s As String
    For i = 1 To Len(a)
        'If Not (IsNumeric(Mid(a, i, 1))) Then
        If IsNumeric(Mid(a, i, 1)) Then
        s = s & Mid(a, i, 1)
        End If
    Next i
replaceNoNum = s
End Function

Pat, thank you for the help.  It is a little too much for me though and I do not know where else I would have to apply in my db and that is what bothers me about going that route.  What is easier for me is using somethign I can at least grasp a little.  ;)

You've been going back and forth on this all day.  You are even writing code!!!  Why not simply try my solution?

In any query where you want to join the two tables, replace the table names with the query names.  That will allow you to join the two and will produce an updateable query.  In Access, select queries and tables are interchangeable for most purposes.

If you have to create your own queries because the tables are different from what you included, all you need to do is to select the columns you need and add the calculated column at the end.  Then save the query.  You can copy the calculated column from the appropriate query.  They are not interchangeable because the field names are different.  I would expect by now though that you understand the way the nested Replace function is working and so should be able to build your own.  The nested replace which others have also recommended is the key to solving this problem.  The Replace() can only handle a single string at a time.  That is why we are nesting the function.  The inner Replace() removes one type of character, the next level takes the modified string and removes another, and the outer Replace() uses the latest version of the modified string and removes the final character type.

did both of you basically say the same?  I see that Pat added some queries and the Replace is very similiar except Pat used Trim.

I included the Trim() because you had it in your original example.  You only have to do the trim once.  Either in the inner replace or the outer one.

I want to be fair on the points but I am using Tom's solution.  I feel like I am limiting Pat's pts because I am not up to speed on Access.  I want all to be on same plane here.

I am good with a split.  There are always alternate solutions in technology.

thanks guys...wish you both a happy new year!