Generate reference to serveral Public variables

[harmi]

Hi NG,

I'm working with Access 97:

if have a big program with a bunch of public vars like:
f.e.:
- sales_2004_jan
- sales_2004_feb
- sales_2004_mar
etc
- sales_2005_jan
- sales_2005_feb
- sales_2005_mar
etc.

Is it possible to generate a reference like:

dim xYear as string
dim xMonth as string
dim xSearch_Public as string

xYear = "2005"
xMonth = "mar"

xSearch_Public = "sales_" & xYear & "_" & xMonth

msgbox xSearch_Public will show: sales_2005_mar

but shoud show: 1200000 (f.e.)

Is it possible to get via xSearch_Public a grip on the value of the
public-variable sales_2005_mar?


Thanks for helping

Regards

Harmi

 

[Douglas J. Steele]

As far as I know, there's no way to refer to a variable that way.

What you consider is to use a 2 dimensional array, so that Sales(1, 1)
contains data for Jan, 2004, Sales(1, 2) contains data for Feb, 2004 and so
on to Sales(1,12) for Dec, 2004, Sales(2, 1) for Jan, 2005 and so on.

 

[Brendan Reynolds]

I can't see a way to do it with the separate variables. It would be easy
enough to do, though, if you could put those variables into an array or a
collection ...

Public Sub TestSub1()

Dim col As Collection
Dim strSales As String
Dim strYear As String
Dim strMonth As String
Dim dblSales As Double

Set col = New Collection
col.Add 1000#, "sales_2004_jan"
col.Add 1100#, "sales_2004_feb"
col.Add 1200#, "sales_2005_jan"
col.Add 1300#, "sales_2005_feb"

strYear = "2004"
strMonth = "feb"
strSales = "sales_" & strYear & "_" & strMonth
dblSales = col(strSales)
Debug.Print dblSales

End Sub

Result in Immediate Window ...

testsub1
1100