FORMAT A CSV

Thank you so much.
--
Glint


"Douglas J. Steele" wrote:

Try something like:

Dim lngLoop1 As Long
Dim lngLoop2 As Long
Dim lngMinValue As Long
Dim lngPlaceHolder As Long
Dim lngPrevValue As Long
Dim strData As String
Dim varValues As Variant

strData = "1,9,17,3,3,7,3"

' Split the comma-separated list into an array

varValues = Split(strData, ",")

' Sort the array into ascending order

For lngLoop1 = LBound(varValues) To UBound(varValues) - 1
lngMinValue = CLng(varValues(lngLoop1))
lngPlaceHolder = lngLoop1
For lngLoop2 = (lngLoop1 + 1) To UBound(varValues)
If CLng(varValues(lngLoop2)) lngMinValue Then
lngMinValue = CLng(varValues(lngLoop2))
lngPlaceHolder = lngLoop2
End If
Next lngLoop2
varValues(lngPlaceHolder) = varValues(lngLoop1)
varValues(lngLoop1) = lngMinValue
Next lngLoop1

' Loop through the sorted array, throwing away duplicates

lngPrevValue = CLng(varValues(0))
strData = varValues(0) & ", "
For lngLoop1 = 1 To UBound(varValues)
If CLng(varValues(lngLoop1)) lngPrevValue Then
lngPrevValue = CLng(varValues(lngLoop1))
strData = strData & varValues(lngLoop1) & ", "
End If
Next lngLoop1

' Delete the extra comma-space from the end of the string

strData = Left(strData, Len(strData) - 2)

strData will now contain unique values.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Glint" wrote in message
news
I give up. Please show me how to do it.
--
Glint


"Douglas J. Steele" wrote:

No, it's an array.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Glint" wrote in message
...
Thanks Douglas.
I am still stuck. Should I treat varValues as a table?
--
Glint


"Douglas J. Steele" wrote:

Dim lngLoop As Long
Dim strData As String
Dim varValues As Variant

strData = "1,9,17,3,3,7,3"
varValues = Split(strData, ",")
For lngLoop = LBound(varValues) To UBound(varValues)
Debug.Print "Element " & lngLoop & " = " & varValues(lngLoop)
Next lngLoop

will product the following output:

Element 0 = 1
Element 1 = 9
Element 2 = 17
Element 3 = 3
Element 4 = 3
Element 5 = 7
Element 6 = 3

I'll leave it to you to figure out how to go from what's in varValues
to
1,
3, 7, 9, 17. (Feel free to come back if you need help...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Glint" wrote in message
...
Thanks a lot, John.
The Split function sounds exactly like what I may need. However, I
have
problems locating an example of it in the Access 07 that I am using
now.
It
does give some explanation of the function, but I cannot find an
exaple
of
its use. Can you please help me?
--
Glint


"John W. Vinson" wrote:

On Sun, 5 Jul 2009 16:22:01 -0700, Glint

wrote:

Hi Guys,
I have a textbox that displays a list of available numbers in a
csv
format:
1,9,17,3,3,7,3 etc. The problem I have is that the numbers come
from
different departments and therefore get repeated. This makes it
difficult for
a user to use the box effectively.
How can I create a table out of the csv, and then make the textbox
display
unique numbers, not repeating a number once it is in the list?

You'll need some VBA code to unpack this string into individual
values.
You
won't want to *create* a table routinely - you should have a table
permanently
in the database, and you can use code to move this data from the
textbox
(or
the text field in your table) into a number field in the table.

See the VBA Help for the "Split" function, it gives an example.
--

John W. Vinson [MVP]

On Mon, 6 Jul 2009 10:21:01 -0700, Glint
wrote:

Thanks a lot, John.
The Split function sounds exactly like what I may need. However, I have
problems locating an example of it in the Access 07 that I am using now. It
does give some explanation of the function, but I cannot find an exaple of
its use. Can you please help me?

Rather than having one bit of code assemble values from other form controls,
and another bit of code take them apart again, why not just take the data
directly rather than (or in addition to) building the contents of the textbox?

--

John W. Vinson [MVP]