Splitting fields

[cynthiav]

Hello,

I am working on a query in Access 2003. I have multiple rows of data like below:

Ord # date description time
1 11/7/06 chocolate 01:00,09:00,18:00

Is there a way in Access to have the information under time split into another field if there is a comma after the number?

Possibly have the times after the first time in the column split into time1, time2, (or more if there are more times) having the times move into a new column if there are more than one time in the field?

I hope that makes sense.

Thanks,
Cynthia

[Killer42]

Do you mean to actually change the way the data is stored in your table, or do you just want to look at it a different way?

[MMcCarthy]

Create new table as follows:

tblTime
TimeValue (Text Datatype)
Ord # (Foreign Key Ref to other table)

Set up a fully cascading relationship between this table and your original table on [Ord#].

You will need to put this function in a module and call in somewhere in form code. Set up a dummy form with a command button if no code available. Make your query the record source of the form and put the Time field on the form and call it txtTime. You will also need to put the [Ord#] field on the form. See underneath for command button code.

 
Function splitTimes(strTime As String, orderNo As Long)
Dim x As Variant
Dim i As Long
 
    x = Split(strTime, ",")
    For i = 0 To UBound(x)
       DoCmd.RunSQL "INSERT INTO tblTime (TimeValue, [Ord#]) " & _
             "VALUES ('" & x(i) & "'," & OrderNo & ");"
    Next i
 
End Sub
 
 

To call the function...

 
Private Sub CommandButtonName_OnClick()
Dim rs As Recordset
 
  Set rs=Me.RecordsetClone
 
  rs.MoveFirst
  Do Until rs.EOF
    splitTimes(Me.txtTime, Me.[Ord#])
    rs.MoveNext
  Loop
 
  rs.Close
  Set rs = Nothing
 
End Sub
 
 

Hello,

I am working on a query in Access 2003. I have multiple rows of data like below:

Ord # date description time
1 11/7/06 chocolate 01:00,09:00,18:00

Is there a way in Access to have the information under time split into another field if there is a comma after the number?

Possibly have the times after the first time in the column split into time1, time2, (or more if there are more times) having the times move into a new column if there are more than one time in the field?

I hope that makes sense.

Thanks,
Cynthia