Solved

Update Query, using Replace

Posted on 2013-12-29
31
304 Views
Last Modified: 2013-12-31
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
Comment
Question by:pdvsa
  • 15
  • 10
  • 6
31 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39745529
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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39745954
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
 

Author Comment

by:pdvsa
ID: 39746024
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39746076
pdvsa,
Did you bother to look at the database I posted?
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746082
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
 

Author Comment

by:pdvsa
ID: 39746124
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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746141
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
 

Author Comment

by:pdvsa
ID: 39746183
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
 

Author Comment

by:pdvsa
ID: 39746203
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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746204
Is it possible that the reference number will have a letter?
Tom
0
 

Author Comment

by:pdvsa
ID: 39746231
Yes, they are both text formatted actually (LCNo and Reference Number)
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746237
Can you provide an example of a LCNO and Reference Number that contain a letter?
0
 

Author Comment

by:pdvsa
ID: 39746279
Here are 2 examples and they reside in both tables:  
CTCS-782784
02102-1378755LDE
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746289
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
 

Author Comment

by:pdvsa
ID: 39746308
<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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:PatHartman
ID: 39746311
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
 

Author Comment

by:pdvsa
ID: 39746312
<Is that how they look in both tables?
==>Yes.  I mean the characters still reside in tblLetterOfCredit and import-CSM2.
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746601
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
 

Author Comment

by:pdvsa
ID: 39746644
<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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39746706
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
 

Author Comment

by:pdvsa
ID: 39746750
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
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 250 total points
ID: 39746771
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39746875
<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
 

Author Comment

by:pdvsa
ID: 39746932
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
 

Author Comment

by:pdvsa
ID: 39746939
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39746974
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
 

Author Comment

by:pdvsa
ID: 39746977
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39747008
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
 

Author Comment

by:pdvsa
ID: 39747013
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
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39747020
I am good with a split.  There are always alternate solutions in technology.
0
 

Author Closing Comment

by:pdvsa
ID: 39748172
thanks guys...wish you both a happy new year!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now