Selecting a string variable using another string variable

[gnawoncents]

I don't know if this is even possible, but I've been searching for a while now with no success. I have a range of integer variables str1, str2, str3, etc. and need to increment their respective value by one (1) when the value of a second string (varNUM) equals the number in the first string.

Here is how I'm setting it up

Dim nstr1 As Integer
Dim nstr2 As Integer
Dim nstr3 As Integer
Dim varNUM As Integer
 
nstr1 = 0
nstr2 = 0
nstr3 = 0
 
varNUM = DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & fldReport & "'") 'this will set varNUM to a value of 1-3
 
nstr(varNUM) = nstr(varNUM) + 1 'this is the part I can't get to work
 

The last line is where I can't figure out the correct syntax to get VBA to recognize it as a string variable.

What I'm hoping for is if the value of varNUM is 2, then the code will increment nstr2 by 1. Can anyone help me with the proper syntax?

[ADezii]

@gnawoncents
I am really confused at to your request, but to Increment a Numeric Value stored as a String, then to convert it back to a String again:

CStr(Val("123") + 1) ==> 124

[gnawoncents]

Sorry, I'll try and be more clear. I have no problem with incrementing the value. What I need to know how to do (or if it's even possible) is select which variable to increment by using another variable. In other words:

nstr1 or nstr2 or nstr3

Select which one based on another variable varNUM which could be 1 or 2 or 3

Consequently, I need to know the syntax for the string. I have tried the following, all of which I know are wrong:

nstr(varNUM)
nstr.varNUM
[nstr](varNUM)
etc.

So if the value of varNUM is 1, then when I say nstr(varNUM) + 1, VBA should read it as nstr1 + 1.

I hope this is somewhat clearer. Thank you for your help.

[ADezii]

@gnawoncents
First of all, it is a reaaaaaally bad idea to preface an Integer Variable with str, but that being said:

varNum = DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & _
         fldReport & "'") 'this will set varNUM to a value of 1-3
 
'varNum will be either 1, 2, or 3, so Increment corresponding Variable
Select Case varNum
  Case 1        'varNum = 1
    str1 = str1 + 1
  Case 2        'varNum = 2
    str2 = str2 + 1
  Case 3        'varNum = 3
    str3 = str3 + 1
  Case Else     'should not happen?
End Select

[gnawoncents]

ADezii,

Thanks, I'll take your advice and change the str (to ... what int?) to something else.

Also, I was oversimplifying the fact when I said there were only three possibilities, which is why I was looking for an option for it to change dynamically for whatever varNum happened to be. However, if this is not possible, I will kindly use your Select Case example. Again, thank you.

[OldBirdman]

The original names of nstr1, nstr2, nstr3, etc. has led you into a mental trap. These are unique names to Access, and might as well have been nstr1st, nstr2nd, nstr3rd, etc. or nstrFirst, nstrSecond, nstrThird, etc. In which case, ADezii's use of the Select Case is correct.

You could declare nstr as an array.

Dim nstr(3) as Integer
'Initialize array to zero
For varNUM = 1 to 3 'or 1 to UBound(nst)
nstr(varNUM) = 0
Next varNUM 
...
nstr(varNUM) = nstr(varNUM) + 1
 

NOTE: I have used your variable names from Post #1, but am in 100% agreement with ADezii that these names are inadvisable. I see you plan to change them.

[ADezii]

I do believe that I have a rather Unique Solution to your dilemma, whereby the dynamically created Variable Names (str1 to str9999...) and their associated Values are stored in a Table named tblValues. The code will first check and see if the Variable Name exists (str & CStr(varNum)). If the Variable Name exists, it will Increment its Value by 1, and if it doesn't exists, it will Append the Variable Name (str & CStr(varNum)) and its Value (varNum) to tblValues.

1. Create a Table named tblValues with only 2 Fields:
   • VarName {TEXT}
   • Value (LONG}
2. Insert the following Code Segment in its appropriate place.
   

   varNum = DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & _
            fldReport & "'") 'this will set varNUM to a value of 1-3
    
   If DCount("*", "tblValues", "[VarName] = 'str" & CStr(varNum) & "'") = 0 Then
     'Does not exist, so create the Variable Name, then Append it and the Value to the Table
     CurrentDb.Execute "INSERT INTO tblValues ([VarName],[Value]) Values('str" & _
                        CStr(varNum) & "'," & varNum & ")", dbFailOnError
   Else
     'Variable Name exists, so just Update the Value Field (Increment by +1)
     CurrentDb.Execute "UPDATE tblValues SET [Value] = [Value] + 1 WHERE [VarName] = 'str" & _
                        CStr(varNum) & "'", dbFailOnError
   End If

3. A simple DCount() and/or DLookup() can see if a Variable exists, and if it does, retrieve its Value.

[gnawoncents]

Thank you both. I'll give these a shot and let you know.

[gnawoncents]

Thanks for all the advice and help. I changed the integers to int (is this the appropriate terminology?) and decided to go with the ADezii's Case example for now. It took some time, but works great so far. Thanks again all.