tbassngal

Hi everyone! I am new to VBA and module programming and what I need to do is create a new table (which isn't working) with the following fields:

Public Sub CreateTableTest()
CREATE TABLE tblProdSched
(ProjectPOId text, ProdQty LONG, ProdDate LONG);
End Sub

In another table, I am manipulating a field called prodnschedule, which I need to split. Currently the one field has data like this:

50 (05/01/05), 100 (05/02/05), 150 (05/03/05), etc.

This data after it is split will go into the new table (the field ProjectPOId) is also in the original table with the above data.

The new data should be:

ProductionPOId ProdQty ProdDate
???? (I don't know how I get this) 50 05/01/05
???? 100 05/02/05
etc.

In those fields, I would like to place the values found by using the split function... PLEASE HELP! I am so new to module programming and I've been given this task to complete asap. I know this is wrong... it's not working.

Public Function fblnMakeTable_F_ProdSched()
Dim a, i, x, strProdSched As String
Set DB = CurrentDb
strSQL = "Select * from R_DeliveryStatus"
Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
strProdSched = rs.[prodnschedule].Value
If strProdSched <> "No New Milestones" And strProdSched <> "" Then
a = Split(Replace(strProdSched, " ", ""), ",")
For Each i In a
x = "ProductionQty=" & Val(Left(i, InStr(i, "(") - 1)) & ", "
x = x & "[ProductionDate]=" & CDate(Mid(i, 1 + InStr(i, "("), 8))
MsgBox x
Next
End If
rs.MoveNext
Loop

rs.Close
End Function

All your help is appreciated!