How do I convert number ranges to lists?

[Jenny]

I am importing data to Access that has a 'Weeks' field containing number
ranges, eg 3-5,7,11-13,17. I need to then use this data to create a new
record for each of the weeks so in this eg I would end up with 8 records -
one for each of weeks 3, 4, 5, 7, 11, 12, 13, 17.

I think this would involve two steps: 1. convert the data to a list, eg
3,4,5,7... and 2. append the additional records (possibly with an append
query?)

Can anybody help with this or point me in the right direction on the help
menu?

Many thanks

Jenny

 

[Pieter Wijnen]

Something like this ought to do it

Sub SplitInput(Rng As String)

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim V As Variant
Dim Pos As Long
Dim i As Long, j As Long

Set Db = Access.CurrentDb
Set Qdef = Db.CreateQueryDef(VBA.vbNullString)
QDef.SQL = "Parameters pWeek Long;" & VBA.vbCrlf & _
"INSERT INTO MyWeeks (WeekNo) VALUES (pWeek)"

V=Split(Rng,",")
For i = LBound(V) To UBound(V)
Pos = VBA.Instr(V(i),"-")
If Pos Then
For j = VBA.Left(V(i),Pos - 1) To VBA.Mid(V(i), Pos + 1)
Qdef.Parameters(0).Value = j
Qdef.Execute, DAO.dbSeeChanges
Next 'j
Else
Qdef.Parameters(0).Value = V(i)
Qdef.Execute, DAO.dbSeeChanges
End If
Next 'i
Qdef.Close : Set Qdef = Nothing
Set Db = Nothing
End Sub

HTH

Pieter

 

[Jenny]

Hi Peter

Thanks a lot for this, unfortunately I'm not that advanced in access. I have
used visual basic but more by editing the code that get's written
automatically when you use the wizards.

Also, I'm not sure which bits I should change to the names of my own
tables/fields and which bits are the code.

If you, or anyone else, can tell me, or give me some more tips, I'd be
really grateful.

Thanks again!

Jenny

 

[KARL DEWEY]

Try this ---
Add two more fields to the table.
Weeks Start End
3-5 0 0
7 0 0
11-13 0 0
17 0 0

Run update query --
UPDATE Jenny SET Jenny.Start = Val([Weeks]), Jenny.[End] =
IIf(InStrRev([weeks],"-")=0,[Weeks],Val(Right([Weeks],InStrRev([weeks],"-")-1)));

Then append query ---
INSERT INTO Jeeny_X ( Weeks )
SELECT [Start]+[CountNUM] AS X
FROM Jenny, CountNumber
WHERE ((([Start]+[CountNUM]) Between [Start] And [End]));