Date Split

[dave]

Hello,

I have been asked to query some data in access here is the situation,

we have a table with some fields like id, daterange, ck1 with values like

100, 20-Oct-05,21-Oct-05,22-Oct-05 ,videoprojector
129, 20-Nov-05,21-Nov-05,22-Nov-05 ,videoprojector

What we would like to do is to separate the comma separated dates like
this,

so we would have separate entries for each date but all with the same id for
that particular row.

id daterange ck
100 20-Oct-05 videoprojector
100 21-Oct-05 videoprojector
100 22-Oct-05 videoprojector
129 20-Nov-05 videoprojector
129 21-Nov-05 videoprojector
129 22-Nov-05 videoprojector

is this possible and if so can anybody help

Regards

Dave

 

[Guest]

I would imagine that the best way to do this would be to copy the data into
an Excel spreadsheet and manipulate it there. I would have two date fields, a
start date and end date, that way you can reprent the range.

Using a MID function you can pull the dates from the string in the daterange
cell and put them into the start and end date cells, then import the Excel
table back into Access.

 

[Douglas J Steele]

Assuming you're using Access 2000 or newer (and that you've got a reference
set to DAO), you could use code like the following:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngId As Long
Dim lngLoop As Long
Dim strCk1 As String
Dim strDateRange As String
Dim strSQL As String
Dim varDateRange As Variant

strSQL = "SELECT Id, DateRange, Ck1 FROM ExistingTable"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

Do While rsCurr.EOF = False
lngId = rsCurr!Id
strDateRange = rsCurr!DateRange
strCk1 = rsCurr!Ck1
varDateRange = Split(strDateRange, ",")
If IsNull(varDateRange) = False Then
For lngLoop = LBound(varDateRange) To UBound(varDateRange)
strSQL = "INSERT INTO NewTable(Id, DateRange, Ck1) " & _
"VALUES(" & lngId & ", " & _
Format(varDateRange(lngLoop), "\#mm\/dd\/yyyy\#") & ", " & _
Chr$(34) & strCk1 & Chr$(34) & ")"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
End If
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


Replace "ExistingTable" and "NewTable" with the appropriate names. (Note
that I'm assuming DateRange is a date field in the new table, not a string)