Hi Alfred,
Nice question. The answer is to do it in VBA. Here is how:
Go to <
http://www.blueclaw-db.com/quick-sort.htm> and copy Janet Loch's
QuickSort code. Paste it into a regular module (not form, report or class).
Change the "Private" on the first line to "Public". There is problem with
this line:
‘ < comparison of the values is a descending sort
Change the grave accent (‘) at the beginning to an apostrophe ('). Add
this code to the same module:
------------------------------------------------------------------------
Public Function FixCitationPages(ByVal strPagesList As String) As String
Dim intIndex As Integer
Dim strPrevious As String
Dim strReturn As String
Dim strarrPages() As String
' Split the stuff into an array
strarrPages = Split(Replace(strPagesList, " ", ""), ",")
intIndex = LBound(strarrPages)
' Sort the array
QuickSort strarrPages, intIndex, UBound(strarrPages)
' Reassemble, skipping duplicates
strReturn = strarrPages(intIndex)
strPrevious = strReturn
Do While intIndex <= UBound(strarrPages)
If strarrPages(intIndex) <> strPrevious Then
strPrevious = strarrPages(intIndex)
strReturn = strReturn & ", " & strPrevious
End If
intIndex = intIndex + 1
Loop
FixCitationPages = strReturn
End Function
------------------------------------------------------------------------
When you need to fix the citation pages simply call the function:
select FixCitationPages(CitationPages)
from tblCitations;
To update your table with the fixed values do something like this:
update tblCitations
set CitationPages = FixCitationPages(CitationPages)
where CitationsPages <> FixCitationPages(CitationPages);
Note that I did not include error checking in the funciton or checking
to see if there was anything to split. I will leave that to you.
Hope that helps,
Clifford Bass