• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Update Query, using Replace

Experts,

I am not sure why the Update query is not working in the attached db.

There are special characters and I am stipping them out.
I have Replace to "replace" any "-" and any "0" but it doesnt seem to want to update.

I only have the suspect record in the db and I have copied this record below between the 2 tables:

0045-000-20002074   (this one has 3 0's 0 between 45 and 2 and "-")
00450020002074        (this one has 2 0's 0 between 45 and 2 and no "-")

My understanding is that the Replace will strip the special character "-" and "0" and compare only on the remaining numbers: 452274 (no 0's or "-")

On my end, the update query has 0 records to update.  
If I MANUALLY remove the "-" in import-CSM2 then it will update but the Replace is suppose to do this automatically (I think)

Do you see what the issue is?    thank you

2010 format
EE---Copy.accdb
0
pdvsa
Asked:
pdvsa
  • 15
  • 10
  • 6
2 Solutions
 
PatHartmanCommented:
The query cannot update anything since it produces a Cartesian Product.  You need to specify a join between the two tables and you can't really do that as it stands.  I created three new queries to solve the problem.  One query for each of the tables to strip out the unwanted characters and the third query is the replacement for the original update query.
EE---Copy-Updated.accdb
0
 
ButlerTechnologyCommented:
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,""),"-","")

Open in new window

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

Tom
0
 
pdvsaAuthor Commented:
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","")));
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PatHartmanCommented:
pdvsa,
Did you bother to look at the database I posted?
0
 
ButlerTechnologyCommented:
UPDATE tblLetterOfCredit, [import-CSM2] SET tblLetterOfCredit.GuaranteeCode = [import-CSM2].[Guarantee Code]
WHERE (((Replace(Replace([LCNo],"-",""),"0",""))=Replace(Replace([LCNo],"-",""),"0","")));

Open 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
0
 
pdvsaAuthor Commented:
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.
0
 
ButlerTechnologyCommented:
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
0
 
pdvsaAuthor Commented:
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.    


#Name
0
 
pdvsaAuthor Commented:
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","")
0
 
ButlerTechnologyCommented:
Is it possible that the reference number will have a letter?
Tom
0
 
pdvsaAuthor Commented:
Yes, they are both text formatted actually (LCNo and Reference Number)
0
 
ButlerTechnologyCommented:
Can you provide an example of a LCNO and Reference Number that contain a letter?
0
 
pdvsaAuthor Commented:
Here are 2 examples and they reside in both tables:  
CTCS-782784
02102-1378755LDE
0
 
ButlerTechnologyCommented:
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?
0
 
pdvsaAuthor Commented:
<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.
0
 
PatHartmanCommented:
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.
0
 
pdvsaAuthor Commented:
<Is that how they look in both tables?
==>Yes.  I mean the characters still reside in tblLetterOfCredit and import-CSM2.
0
 
ButlerTechnologyCommented:
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.
0
 
pdvsaAuthor Commented:
<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...
0
 
ButlerTechnologyCommented:
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));

Open in new window

0
 
pdvsaAuthor Commented:
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]," ",""),"/","")));
0
 
ButlerTechnologyCommented:
You will need to further nest the replace function with  nesting for each adjustment.

Replace(Replace(Replace(Replace([LCNO], "-", ""), "0", ""), "/", ""), " ", "")
Replace(Replace(Replace(Replace([Reference Number], "-", ""), "0", ""), "/", ""), " ", "")

It does get a little messy.  If you have any more characters that need to be replace, I would recommend that you switch to a VBA function to handle the converstion.

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

Open in new window

0
 
PatHartmanCommented:
<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.
0
 
pdvsaAuthor Commented:
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
0
 
pdvsaAuthor Commented:
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.  ;)
0
 
PatHartmanCommented:
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.
0
 
pdvsaAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
pdvsaAuthor Commented:
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.
0
 
ButlerTechnologyCommented:
I am good with a split.  There are always alternate solutions in technology.
0
 
pdvsaAuthor Commented:
thanks guys...wish you both a happy new year!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 15
  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now