FORMAT A CSV

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?
--
Glint

Is the text box in a form?

How is the text box populated now?

What do the users do with the data in this text box?

If you get the csv data into a table, you could create a query with either a
DISTINCT clause or a Totals query. That would eliminate duplicates from
showing. Then you might be able to use this query as the source for the text
box. You could even sort the numbers using the query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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?
--
Glint

Thanks Jerry.
The textbox is an unbound one in the form, and is populated by an iteration
through other bound textboxes in the form which displays one record. Any
textbox that has the items are then listed as csv in the unbound textbox.
A user can then know where to place other items based on the position of
existing items.
--
Glint


"Jerry Whittle" wrote:

Is the text box in a form?

How is the text box populated now?

What do the users do with the data in this text box?

If you get the csv data into a table, you could create a query with either a
DISTINCT clause or a Totals query. That would eliminate duplicates from
showing. Then you might be able to use this query as the source for the text
box. You could even sort the numbers using the query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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?
--
Glint

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]

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]

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]

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]

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]

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]

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]