Variable as a Variable? (1 Viewer)

[kjohnson]

I'm wondering if it is possible to set a Variable as a Variable.

For example...
A set of variables all starting with the same name that are appended with numbers. such as Var1, Var2, Var3, etc.

I want to run a loop that goes through this list of variables by using a count, so that I do not have to type out every variable in the list.

Such that...
Count = 1 // yields Var1
Count = 2 // yields Var2
Count = 3 // yields Var3

Then when the count is equal to 1, the variable pulls from Var1
And when the count is equal to 2, the variable pulls from Var2
And etc...

I have tried building it like this...
Dim Variable As String
Variable = "[Var" & [Count] & "]"
But it only returns a string like "[Var1]" not what Var1 is set as

This would be helpful to cut down on typing static code and to make the code as dynamic as possible to allow for adaptations.

Any ideas?

 

[MSAccessRookie]

I'm wondering if it is possible to set a Variable as a Variable.

For example...
A set of variables all starting with the same name that are appended with numbers. such as Var1, Var2, Var3, etc.

I want to run a loop that goes through this list of variables by using a count, so that I do not have to type out every variable in the list.

Such that...
Count = 1 // yields Var1
Count = 2 // yields Var2
Count = 3 // yields Var3

Then when the count is equal to 1, the variable pulls from Var1
And when the count is equal to 2, the variable pulls from Var2
And etc...

I have tried building it like this...
Dim Variable As String
Variable = "[Var" & [Count] & "]"
But it only returns a string like "[Var1]" not what Var1 is set as

This would be helpful to cut down on typing static code and to make the code as dynamic as possible to allow for adaptations.

Any ideas?

Have you considered using an Array? If there is a fixed number of variables that you are currently using, an Array might be a solution.

 

[Banana]

I'm not exactly sure what you are after here.

If you need to be able to control the number of multiple loops, surely something like this would fit the bill.

Dim Outer As Long
Dim Middle As Long
Dim Inner As Long

Dim i As Long
Dim j As Long
Dim k As Long

Outer = 10
Middle = 20
Inner = 30

For i = 0 to Outer
   For j = 0 to Middle
      For k = 0 to Inner
         Debug.Print i & ";" & j & ";" & k
      Next
   Next
Next

 

[kjohnson]

Um...

I need to variablize a variable name... if that makes sense.

e.g. use the variable [var] to equal [var1], then [var2], then [var3], etc.
but I dont want to hard code the 1,2,3,etc. part

I am hoping I can use a counter variable to define which variable to use.

when [counter] = 1 then [var] = [var1]
when [counter] = 2 then [var] = [var2]

Its hard to put this into words, I hope that helps some as an explanation of what i am trying to do. And im not even sure if it is possible.

 

[MSAccessRookie]

Um...

I need to variablize a variable name... if that makes sense.

e.g. use the variable [var] to equal [var1], then [var2], then [var3], etc.
but I dont want to hard code the 1,2,3,etc. part

I am hoping I can use a counter variable to define which variable to use.

when [counter] = 1 then [var] = [var1]
when [counter] = 2 then [var] = [var2]

Its hard to put this into words, I hope that helps some as an explanation of what i am trying to do. And im not even sure if it is possible.

It sounds like a good opprtunity to use an array similar to the one in the example below:

Dim Rainbow() as String=("Red", "Orange", "Yellow", "Green", "Blue", "Indigo", "Violet")
 
In this case:
[INDENT]Rainbow(0) = "Red"
Rainbow(1) = "Orange"
Rainbow(2) = "Yellow"
Rainbow(3) = "Green"
Rainbow(4) = "Blue"
Rainbow(5) = "Indigo"
Rainbow(6) = "Violet"
[/INDENT]

In your Loop, the Array elements can be referred to by the incremental pointer.

 

[Banana]

Well, MSAccessRookie gave you the answer, but to be honest, it is still not clear what you actually want to do with it. Another reason is that there may exist a better solution if we understood more about what you ultimately intend to do it, How are you going to use it? Some sample code, even a mock-up mirroring your intention, wouldn't hurt.

 

[kjohnson]

I understand the array now, and I like that idea, but I forgot to mention that I need to fill an Access Form with this.

Another reason is that there may exist a better solution if we understood more about what you ultimately intend to do it, How are you going to use it? Some sample code, even a mock-up mirroring your intention, wouldn't hurt.

Ya, I'm hoping this example will help (I was actually typing it up as you responded)

This is what I need to fill

------------Scholarship #1-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.
------------Scholarship #2-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.
------------Scholarship #3-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.

The scholarship number will increment by 1 each time.
And instead of hard coding the scholarship number, use a counter.

And in similar fashion, I have named the [# of Students] fields as the fields below.
Below is also my code (in long form and all hard coded)

[ProvostSch_1] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 3.5 And [CAREER_GPA] <= 4")
[ProvostSch_2] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 3 And [CAREER_GPA] <= 3.49")
[ProvostSch_3] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 2.5 And [CAREER_GPA] <= 2.99")
[ProvostSch_4] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 2 And [CAREER_GPA] <= 2.49")
[ProvostSch_5] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 1.5 And [CAREER_GPA] <= 1.99")
[ProvostSch_6] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] >= 1 And [CAREER_GPA] <= 1.49")
[ProvostSch_7] = DCount("[ID_Num]", "2008_10_ProvostSch_36", "[CAREER_GPA] < 1")

[PresNewSch_1] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 3.5 And [CAREER_GPA] <= 4")
[PresNewSch_2] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 3 And [CAREER_GPA] <= 3.49")
[PresNewSch_3] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 2.5 And [CAREER_GPA] <= 2.99")
[PresNewSch_4] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 2 And [CAREER_GPA] <= 2.49")
[PresNewSch_5] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 1.5 And [CAREER_GPA] <= 1.99")
[PresNewSch_6] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 1 And [CAREER_GPA] <= 1.49")
[PresNewSch_7] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] < 1")

 

[MSAccessRookie]

Well, MSAccessRookie gave you the answer, but to be honest, it is still not clear what you actually want to do with it. Another reason is that there may exist a better solution if we understood more about what you ultimately intend to do it, How are you going to use it? Some sample code, even a mock-up mirroring your intention, wouldn't hurt.

I was not sure why you needed what you asked for, but I thought that an Array might be able to get you there, and I agree with Banana that if we could see what you were trying to do, we could assist you much better.

 

[WayneRyan]

Kjohnson,

Everything is repeating, shouldn't this be just a simple "Group By", maybe
with a partition (or some introductory query)?

You really should post your table structures and some sample data.

------------Scholarship #1-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.
------------Scholarship #2-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.
------------Scholarship #3-------------------
- GPA between 3.5 and 4.0 -- [# of Students] --
- GPA between 3.0 and 3.5 -- [# of Students] --
- etc.


[PresNewSch_1] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 3.5 And [CAREER_GPA] <= 4")
[PresNewSch_2] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 3 And [CAREER_GPA] <= 3.49")
[PresNewSch_3] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 2.5 And [CAREER_GPA] <= 2.99")
[PresNewSch_4] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 2 And [CAREER_GPA] <= 2.49")
[PresNewSch_5] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 1.5 And [CAREER_GPA] <= 1.99")
[PresNewSch_6] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] >= 1 And [CAREER_GPA] <= 1.49")
[PresNewSch_7] = DCount("[ID_Num]", "2008_10_PresNewSch_36", "[CAREER_GPA] < 1")

Wayne

 

[ajetrumpet]

I'm not exactly sure what you are after here.

If you need to be able to control the number of multiple loops, surely something like this would fit the bill.

Dim Outer As Long
Dim Middle As Long
Dim Inner As Long

Dim i As Long
Dim j As Long
Dim k As Long

Outer = 10
Middle = 20
Inner = 30

For i = 0 to Outer
   For j = 0 to Middle
      For k = 0 to Inner
         Debug.Print i & ";" & j & ";" & k
      Next
   Next
Next

i'm gonna start calling you mr. ALGORITHM boy, banana. that function looks like a mutating algorithm someone would write for math-based security or something. it's almost so abbreviated, it looks like C. laughing...


JOHNSON,

if i may chime in here, i think wayne is right. structure is important here. if you want my opinion, you can do what you want with some good ol fashion loops, conversion functions and integer variables. i just dont think that you have any concrete responses here, because the structure is so important as a source of how to write the code to give you the output in the right format.

 

[kjohnson]

hhmm...
sorry if this is hard to understand what i am trying to say, it is also hard to figure out how to say... lol

I have variables to represent ranges,
e.g.
[ProvostSch_1] is the first GPA range for the ProvostSch Scholarship students.
[PresNewSch_1] is the first GPA range for the PresNewSch Scholarship students.
etc., etc., etc.

And I have a from set up to run the code at the opening of the form.
And it is to assign values based on DCounts from Queries.
The form is set up as such

------- Scholarship Name-------------
-- Range 1 Label ---- Range 1 Count--

There will always be 7 GPA ranges for each Scholarship.
So what I have attempted to do is create a loop with a variable variable name using a counter variable to determine which GPA range it is DCounting for. For example,

the first time through the loop would be
Count = 1
Scholarship Name = ProvostSch_1

the second time through the loop would be
Count = 2
Scholarship Name = ProvostSch_2

What I had set up was something like
Scholarship Name = [Scholarship Name Prefix] & "_" & [Count]

but I want to use [Scholarship Name] as a variable to assign the value returned by the DCOunt.
e.g.
When Count = 1
[Scholarship Name] = DCount (......)
being the same as
[ProvostSch_1] = DCount(....)
AND
When Count = 2
[Scholarship Name] = DCount (......)
being the same as
[ProvostSch_2] = DCount(....)

(Eliminating Hard Coating and Use Constants)

I hope that explanes it a little better at least.
And the point of this would be that I could make an application out of this, and have it where if the scholarship name was to change, I would only have to change the Scholarship Name Prefix parts as Constants.

And Thank you all will trying to help me with this.
Even if I dont figure this out, this Forum has helped me learn so much dealing with Access and VBA in general.

 

[ajetrumpet]

johnson,

it still isn't clear. i'm afraid you'll have to upload the database for someone to look at in order to get a fix. you'll also probably have to mark it up with so we know what is what and what is where and what to do....

 

[petehilljnr]

If I am interpreting correctly from your original post:

I have tried building it like this...
Dim Variable As String
Variable = "[Var" & [Count] & "]"
But it only returns a string like "[Var1]" not what Var1 is set as

If [VarX] is the name of a control on your form (which I'm interpreting as is the case because it is wrapped in square brackets), then all you need to do return the value of the control [VarX] is:

Dim Variable As String
Dim MyResult as [COLOR=red][B]Variant  <--- or whatever type of data the control is holding[/B][/COLOR]
Variable = "[Var" & [Count] & "]"
MyResult = Me.Controls(Variable).Value

Was it as simple as that?

Regards,
Pete

 

[The_Doc_Man]

After thinking about this for a few minutes, I'm going to say that I don't know of any syntax that would allow you to do this in a truly general case.

Because variables are in modules that are at least semi-compiled, I don't think you have a simple way to establish a run-time reference that is totally arbitrary. Compilation, even for semi-compilers, draws a line at runtime references. And that is because there is no collection of variables (that I've ever seen).

You can build a dynamic run-time reference to a table because there is a collection of tables in the DB workspace. Ditto queries. On a form or report you have a collection of controls. But I don't know of a collection of variables. Collections allow you to do name searches. If there is no collection, there is no name search.

If you have a non-general case, there might be a way. If you have a long but predictable list of possible variable names, a SELECT CASE statement might do it.

In a general module:

  {in the declarations section}

Dim Var1 as Long
Dim Var2 as Long
Dim Var3 as Long
Dim Var4 as Long
...
Dim Var98 as Long
Dim Var99 as Long
Dim Var100 as Long

{sometime later in same module}

Public Function PickVar( varname as String ) as Long

SELECT CASE varname
    CASE "Var1" PickVar = Var1
    CASE "Var2" PickVar = Var2
    CASE "Var3" PickVar = Var3

    etc. etc.

   CASE ELSE PickVar = 0
END SELECT

End Sub


{somewhere else}

    J = PickVar( "VAR" & CStr(VarNumber) )

This would work if the prefix was constrained and the range of possible variables was constrained. You could never select more variables than you had declared because the SELECT CASE would stop you from naming an undeclared variable. This is massively ugly but the only way I know to do exactly what you described.

If this is a general case with unconstrained variable names, it would be impossible to do this. If this is a highly constrained case, you have a shot at it.

 

[kjohnson]

Ok The_Doc_Man,

That seems to make sense to me.
I can achieve the outcome I want, but I was hoping of a different way of doing it.
And you post I think explained what I was looking for.
I don't think what I was hoping for is possible, but I figured I'd take a hack at it.

And thank you all for your help, this forum is the best place for Access Help and the like that I have ever found.