By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Expanding data - 1-5 to 1, 2, 3, 4, 5

P: n/a
Hi,

Using Access 2000 on a 2000 Workstation.

Need to get data that's been entered as "Wks: 2-6, 8-11" (for example) into
the format: "2, 3, 4, 5, 6, 8, 9, 10, 11", and finally into a checkbox format
(ie each column has a number above it, and a corresponding tickbox), but I
can probably get that checkbox bit done myself.

Anyhoo,

What's going to be the best way to do this? I'm struggling to find a method
to do this.

Cheers,

Gary
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
In message <sl*********************@profile.l33t-d00d.co.uk>, Gary Smith
<sh******@l33t-d00d.co.uk> writes
Hi,

Using Access 2000 on a 2000 Workstation.

Need to get data that's been entered as "Wks: 2-6, 8-11" (for example) into
the format: "2, 3, 4, 5, 6, 8, 9, 10, 11", and finally into a checkbox format
(ie each column has a number above it, and a corresponding tickbox), but I
can probably get that checkbox bit done myself.

Anyhoo,

What's going to be the best way to do this? I'm struggling to find a method
to do this.


Probably the first step would be to go through the string extracting
tokens, each token being either a number or an operator. Then do a
second pass through the list of tokens.

If the token(n) is numeric then check whether token(n+1) is another
number. If so add the value of token(n) to your list and increment n. If
token(n+1) is a "-" then extract token(n+2) and add every number between
the values of token(n) and token(n+2).

As this looks like the sort of data that has been entered by hand I
think you may need plenty of error-checking code.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #2

P: n/a
Hi, sorry, that perhaps wasn't explained too well.

"Wks: 2-6, 8-11" (or whatever) refer to week numbers - ie "2-6" means
"2, 3, 4, 5, 6" and "8-11" means "8, 9, 10, 11", ergo, the - means "to"
and shouldn't be taken as an operator. But the token idea does sound
like a plan in some respects. I'll investigate the possibility of that
one after lunch :)

Cheers,

Gary

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
Gary Smith <sh******@l33t-d00d.co.uk> wrote in message news:<sl*********************@profile.l33t-d00d.co.uk>...
Hi,

Using Access 2000 on a 2000 Workstation.

Need to get data that's been entered as "Wks: 2-6, 8-11" (for example) into
the format: "2, 3, 4, 5, 6, 8, 9, 10, 11", and finally into a checkbox format
(ie each column has a number above it, and a corresponding tickbox), but I
can probably get that checkbox bit done myself.

Anyhoo,

What's going to be the best way to do this? I'm struggling to find a method
to do this.

Cheers,

Gary


So once you've parsed out and filled in the sequence, you're going to
write this expanded sequence (1-5:--> {1,2,3,4,5}) to a table as a
sequence of individual records?

here's some really ugly code that seems to work... Your mileage may
vary.

Option Compare Database
Option Explicit

Public Sub ExpandList(ByVal strList As String)

Dim strTemp As String
Dim varExpandedList As Variant
Dim varStartStop As Variant
Dim intCounter As Integer
Dim intCounter2 As Integer
Dim intPos As Integer
Dim intLow As Integer, intHigh As Integer

strTemp = strList
varExpandedList = Split(strTemp, ",")

Debug.Print "strTemp: " & strTemp

For intCounter = 0 To UBound(varExpandedList)
intPos = InStr(1, Trim(varExpandedList(intCounter)), "-")

If intPos > 0 Then
varStartStop =
Split(Trim(varExpandedList(intCounter)), "-")
For intCounter2 = CInt(varStartStop(0)) To
CInt(varStartStop(1))
'--If you're going to append this to a table, do
it here...
Debug.Print intCounter2
Next intCounter2
Else
'--If you're going to append this to a table, do it
here...
Debug.Print CInt(varExpandedList(intCounter))
End If

Next intCounter
End Sub
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.