Query - auto fill "between values"?

[Mikael Lindqvist]

Hi,

I have a table (not created by me) that have two fields (A and B) where each
represent extreme values in an interval.

For example:
A, B
5, 9
2, 4
..
..

I want to run a query that creates another field that shows all the values
BETWEEN (and including) "A" and "B" (e.g. recordset 1 would be: 5, 6, 7, 8, 9
and recordset 2 would be: 2, 3, 4).

Any help much appreciated! :>

Kindly,
Mikael
Sweden

 

[John Spencer]

Do you want one record for each set of values or do you want multiple
records for each set of values?

A B C
5, 9 5,6,7,8,9

Or
A B C
5, 9 5
5, 9 5
5, 9 5
5, 9 5
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mikael Lindqvist]

I want the first:
A B C
5, 9 5,6,7,8,9

Kindly,
Mikael

 

[John Spencer]

DARN - Wrong key

Which of the following do you want?
A B C
5,9 5,6,7,8,9

OR
A B C
5,9 5
5,9 6
5,9 7
5,9 8
5,9 9

The solutions differ. If the first, I would use a custom VBA function to
return the list of values

Public Function fCreateSeries(StartNum, EndNum)
Dim strReturn as String
Dim i as Long
If isNumeric(StartNum) = False or IsNumeric(EndNum) Then
fCreateSeries = StartNum & "- " & EndNum
Else
For i = Val(StartNum) to Val(EndNum)
strReturn = strReturn & i & ", "
Next i
fCreateSeries = Left(strReturn, Len(strReturn)-2)
End If
End Function

If the second post back for a solution.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..