I am trying to take one semi-colon separated field (Custodian) which contains multiple names and may or may not contain duplicate names. I split the field based on semicolon and created new ones for each name in Custodian (Cust0, Cust1, Cust2, etc.) Now I need to parse out the duplicates and combine the non-dupe fields to make the NewCustodian field, but am having a hard time with it. I made code that will mark each duplicate entry in the table tblDupes, which contains the fields DOCID, 0,1,2,3,4,5,6,7 ,8,9,10. I use the following code to mark which fields are dupes. This works okay except that if Cust0 and Cust4 are duplicates, it will enter -1 into Fields 0 and 4 in the tblDupes table, when I actually want to keep the values from one of the fields to concatenate into my new dupe free NewCustodian field. - Function DeDupe()
-
Dim DB As DAO.Database
-
Dim RS As DAO.Recordset
-
Dim strSQL As String
-
Dim arMulti() As String
-
Dim strOrigCust As String
-
Dim intUbound As Integer
-
Dim i As Integer
-
-
Dim strCust0 As String
-
Dim strCust1 As String
-
Dim strCust2 As String
-
Dim strCust3 As String
-
Dim strCust4 As String
-
Dim strCust5 As String
-
Dim strCust6 As String
-
Dim strCust7 As String
-
Dim strCust8 As String
-
Dim strCust9 As String
-
Dim strCust10 As String
-
Dim strCust11 As String
-
Dim intFirstFind As Integer
-
Dim intDupe1 As Integer
-
Dim intDupe2 As Integer
-
Dim intDupe3 As Integer
-
Dim strControlID As String
-
-
-
-
-
Set DB = CurrentDb
-
strSQL = "Select * From qryDupe1_MultipleCust"
-
Set RS = DB.OpenRecordset(strSQL)
-
-
Dim strDupesSQL As String
-
Dim DupeRS As DAO.Recordset
-
-
-
-
-
-
-
-
RS.MoveFirst
-
Do Until RS.EOF
-
strOrigCust = RS!Custodian
-
strControlID = RS![DOCID]
-
arMulti() = Split(strOrigCust, ";")
-
intUbound = UBound(arMulti())
-
strDupesSQL = "Select * from tblDupes Where ControlID = '" & strControlID & "'"
-
Set DupeRS = DB.OpenRecordset(strDupesSQL)
-
-
For i = 0 To intUbound
-
-
-
intFirstFind = InStr(1, strOrigCust, Nz(Trim(RS("Cust" & CStr(i)))))
-
If InStr(intFirstFind + 1, strOrigCust, Trim(RS("Cust" & CStr(i)))) Then
-
'Set DupeRS = "Select * from tblDupes"
-
DupeRS.Edit
-
DupeRS(CStr(i)) = -1
-
DupeRS.Update
-
'intDupe2 = InStr(intFirstFind + 1, strOrigCust, RS("Cust" & CStr(i)))
-
-
' RS.Edit
-
' RS("Cust" & CStr(i)) = Null
-
' RS.Update
-
'
-
End If
-
Next
-
RS.MoveNext
-
Loop
-
-
MsgBox "Fields parsed", vbCritical, "FILE'S DONE"
-
-
-
End Function
-
Any advice is welcome and I hope this is not a double post. I posted this before and nothing showed up on the blog.
7 1257 jimatqsi 1,276
Recognized Expert Top Contributor
Here's one way I might that sort of thing. - Do Until RS.EOF
-
strOrigCust = RS!Custodian
-
strControlID = RS![DOCID]
-
arMulti() = Split(strOrigCust, ";")
-
intUbound = UBound(arMulti())
-
strDupesSQL = "Select * from tblDupes Where ControlID = '" & strControlID & "'"
-
Set DupeRS = DB.OpenRecordset(strDupesSQL)
-
-
strOrigCust="" ' init our string so we can rebuild it
-
For i = 0 To intUbound
-
strOrigCust=replace(strOrigCust & ";","") ' remove this one if it is already there
-
strOrigCust = strOrigCust & arMulti(i) & ";" ' now add this one
-
next
-
rs.edit
-
rs!Custodian=strOrigCust ' replace with our updated string
-
rs.update
-
-
'' intFirstFind = InStr(1, strOrigCust, Nz(Trim(RS("Cust" & CStr(i)))))
-
'' If InStr(intFirstFind + 1, strOrigCust, Trim(RS("Cust" & CStr(i)))) Then
-
'' 'Set DupeRS = "Select * from tblDupes"
-
'' DupeRS.Edit
-
'' DupeRS(CStr(i)) = -1
-
'' DupeRS.Update
-
'' 'intDupe2 = InStr(intFirstFind + 1, strOrigCust, RS("Cust" & CStr(i)))
-
-
' RS.Edit
-
' RS("Cust" & CStr(i)) = Null
-
' RS.Update
-
'
-
'' End If
-
'' Next
-
RS.MoveNext
-
Loop
-
jimatqsi 1,276
Recognized Expert Top Contributor
Of course you could substitute this for lines 11 and 12 - if instr(strOrigCust,arMulti(i) & ";")=0 then
-
strOrigCust=strOrigCust & arMulti(i) & ";"
-
end if
zmbd 5,501
Recognized Expert Moderator Expert
My vote would be to use the instr() as J does in post#3.
Using your upperbound on the array and a for..next loop to step thru the array and avoid the potential bloat from temp-tables and the overhead of a record-set.
Out of curiosity why store the names in a single field, normally, I would have thought that a table to store the names as individual records and then concatenate them as needed via query just to avoid such a mess would be preferable.
NeoPa 32,567
Recognized Expert Moderator MVP
I'd look at digging up a sort routine from the web then working with the sorted results.
Thanks for the advice. It worked a treat.
ZMBD, the semicolon separated fields are not from Access but rather are Relativity data, a Lit Support application which uses SQL Server for a back end and it is known as a "Multi Choice" type field and stores data in these fields in semicolon delimited format.
zmbd 5,501
Recognized Expert Moderator Expert
eeegads the multi-value field somewhere else other than in an Access database!
shudder and tremble in fear the world is coming to an end (@_@)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: * |
last post by:
I need to devise a way of reading the following XML data and taking the
Job_ID and putting into a database using the access_code and the
adt_company_id.
any idea of how I might be able to do this, im stuck!
<?xml version="1.0" encoding="Windows-1252"?>
<Sections>
<SECTION compulsory="0">
|
by: burn_hall |
last post by:
Hi,
I have a problem and can't figure it out and need your help, please
look at the following code and the output also a xml file snippet is
down there too.
Looking at the output I don't know why it's not according to the
code.
Following code is pretty much clear: When method starts execution
<Pat> Node is passed it has childNodes
Inside for-loop
|
by: bird |
last post by:
?sitem
<?xml version="1.0"?>
<RootNode> ' one root node
<Transaction> ' doc element node
<AccountNumber>1001000</AccountNumber> ' node list/children
<Amount>100.00</Amount>
</Transaction>
</RootNode>
|
by: rufus |
last post by:
Hi,
I need to parse some html that contains the comments: <!-- Start Display
---> and <!-- End Display ---> . I need to capture all the HTML between
these comments. I started with: r = New Regex("Start Display --->(.*)<!--
End Display --->") but it didn't seem to match anything. I also tried using
the Substring function in the string library but it didn't match the first
comment for some reason ie strTemp.IndexOf("<!-- Start ...
|
by: Kota |
last post by:
Hi,
I am using webservice to get data from database and using in VBA of visio.
in web service I am using dataset.GetXml() method to return XML string.
using this string I want to parse required node using MSXML3
How we can parse required node instead of looping all the nodes.
Thanks,
| |
by: Seth |
last post by:
First off, my apologies if this is in the wrong newsgroup, but I hope
I'm close enough.
I'm trying to do some parsing of a CSV file using OleDbConnection, but
for some reason, when I populate my DataSet, it is trimming the trailing
spaces. Anybody know why?
Here is my code:
System.Data.OleDb.OleDbConnection connection = null;
|
by: REG |
last post by:
Hi All,
Could someone point me in the right direction ... have been searching for days for a code snippit or a tuitorial that will show how to:
1. Load XML data into an Html Page in a way that is compatible with most browsers
2. Using Javascript then search on that data using more then one criteria. For example dropdown list one = tour location; dropdown list two = tour duration; dropdown list three = tour price
Thank you in advance
...
|
by: hzgt9b |
last post by:
Using VB.NET under .NET 1.1 in VS2003,
BACKGROUND
I have a windows application that dereferences the MsHTM.dll. The app
is successfully able to parse existing HTM documents allowing me to
access the DOM objects as needed.
With my latest changes I want to utilize the MsHTM.dll to parse some
text for me. For instance, I'd like to be able to parse the following
|
by: Jon86 |
last post by:
Hi All,
Please help me in developing a windows application for parsing a page form webbrowser control into a tree view control in the windows form, using C# ..
Cheers
Jon
|
by: sbettadpur |
last post by:
We are developing a recruitment tool.For that I want to do ''Resume Parsing' in PHP.I was able to parse Email-id and Phone No. .But i do not know how to parse the candidate's full name and address from the resume (in various formats like doc,html,rtf,txt etc.) .Can you help me to do this ?
Thank You
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |