How do I convert number ranges to lists?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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]));
 
Back
Top