FORMAT A CSV

  • Thread starter Thread starter Glint
  • Start date Start date
G

Glint

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?
 
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.
 
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.
 
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?
Click to expand...

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.
 
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?
 
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...)
 
No, it's an array.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Glint said:
Thanks Douglas.
I am still stuck. Should I treat varValues as a table?
Click to expand...
 
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.
 
Thank you so much.
--
Glint


Douglas J. Steele said:
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.
Click to expand...
 
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?
Click to expand...

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?
 
Back
Top