Using Split() function from a ListBox

[Isadore]

The following example uses the Split() function to calculate totals from a list box.

The following fields were used as a Row Source to a list box (lstTotal):

poid: ID
Ct: # of entries
Qty: quantity for each order
Catid: Catagory ID

An example record:

poid: 3
Ct: 12
Qty: 1,2,1,3,1,2,1,1,5,1,3,1
Catid: 4167-G,7166-G,4169-H,6141WT-G,4170WT-G,T-2311-J,4493PS-H,7520-H,4483-E,4484-J,4487WT-H,2218-G

For record 3 there were 12 items ordered, the quantity for each item given in the Qty string, and the category id for each item given as Catid. In the example below the year is retrieved and the price is looked up from a separate table dependent on the year ordered. Finally a query is used to update the Total for the order using [poid] and [Total] as hidden textboxes on the form.

Dim i As Integer
Dim intct As Integer
Dim myct As Integer
Dim lngpoid As Long
Dim dteYr As Date
Dim ctlSource As ListBox
Dim intCurrentRow As Integer
Dim intqty As Integer
Dim strToProcess As String
Dim strqty As String
Dim strCat As String
Dim dblLookUp As Double


Private Sub Command0_Click()
DoCmd.SetWarnings False
Set ctlSource = Forms![frmTotal]![lstItems]
For intCurrentRow = 0 To ctlSource.ListCount - 1
  [Total] = 0
  [poid] = ctlSource.Column(0, intCurrentRow)
  intct = ctlSource.Column(1, intCurrentRow)
  strqty = ctlSource.Column(2, intCurrentRow)
  strToProcess = ctlSource.Column(3, intCurrentRow)
  dteYr = DLookup("DateOrdered", "tblChemOrder", "poid=" & [poid])
  If Year(dteYr) = 2008 Then
    For i = 0 To intct - 1
      strCat = Split(strToProcess, ",")(i)
      intqty = Split(strqty, ",")(i)
      dblLookUp = DLookup("UC2008", "tblChemicals", "CatNumber='" & strCat & "'")
      [Total] = [Total] + intqty * dblLookUp
    Next i
   ElseIf Year(dteYr) = 2007 Then
    For i = 0 To intct - 1
      strCat = Split(strToProcess, ",")(i)
      intqty = Split(strqty, ",")(i)
      dblLookUp = DLookup("UC2007", "tblChemicals", "CatNumber='" & strCat & "'")
      [Total] = [Total] + intqty * dblLookUp
    Next i
   ElseIf Year(dteYr) = 2006 Then
    For i = 0 To intct - 1
      strCat = Split(strToProcess, ",")(i)
      intqty = Split(strqty, ",")(i)
      dblLookUp = DLookup("UC2006", "tblChemicals", "CatNumber='" & strCat & "'")
      [Total] = [Total] + intqty * dblLookUp
    Next i
   End If
    DoCmd.OpenQuery "qyUpdateTotal"
Next
DoCmd.SetWarnings True
End Sub

Not rocket science but thought it might be helpful for anyone looking to use the Split() function.

 

[MajP]

Sorry but this looks like an overly complicated solution for a bad data base design. A properly normalized database would not need a band-aid like this.

Qty: 1,2,1,3,1,2,1,1,5,1,3,1
Catid: 4167-G,7166-G,4169-H,6141WT-G,4170WT-G,T-2311-J,4493PS-H,7520-H,4483-E,4484-J,4487WT-H,2218-G

Repeating groups within columns violates first normal form.

 

[PHV]

Not to mention the tblChemicals table having fields named UC2006, UC2007, UC2008
Have a look here:

http://r937.com/relational.html

 

[Isadore]

I selected the "light bulb" and so did not intend to write a question. The purpose of the post was to demonstrate one example of using the "split" function in case someone down the road did a search, as I did.

I did not criticize the design of the table, which was not of my doing, but good point, yes, in a well designed table there should have been a one-to-many table with the requisite values.

The filed names UC2006, UC2007, UC2008 were put there by me temporarily to calculte past year values.

So, good points on the design critique, I appreciate those comments and for anyone reading this they will be alerted to these pitfalls - sorry I should have mentioned them as well.

 

[SkipVought]

If you have a SUGGESTION rather than a QUESTION, then write an FAQ.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Isadore]

Thanks for the tip Skip; and all other suggestions here.

You guys keep up the good work!

 

[SeeThru]

I actually wrote a custom function do do the same thing as you have split doing there - could be useful for the future.

Thanks

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[Isadore]

SeeThru - Thanks for the input. When I was confronted with the conditions as stated above I had no choice but to turn to the Split() function - and hence a search here at Tek-Tips for a few clues on how to approach the problem (I program primarily in VB ASP.NET).

Perhaps SeeThru you could share your function on this thread and in the future should someone search through Tek-Tips looking to break up strings it would prove useful (and time saving!). Of course I would enjoy seeing it as well.