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?
@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
8 3154 @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
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.
@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
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.
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.
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. - Create a Table named tblValues with only 2 Fields:
- VarName {TEXT}
- Value (LONG}
- 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
- A simple DCount() and/or DLookup() can see if a Variable exists, and if it does, retrieve its Value.
Thank you both. I'll give these a shot and let you know.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stefan Mueller |
last post by:
I'd like to set a variable called 'FocusIsOn' if a button got the focus.
Because my button is dynamically created I do it like
xelement = document.createElement("input")
xelement.type = "button"...
|
by: John |
last post by:
How can I set a variable (Table_Select) equal to another string variable (Table_1)?
Example:
--------------------------------
Table_1 = "Winner"
For i = 1 to 3
Table_Select = "Table_" & i...
|
by: rajendra.stalekar |
last post by:
Hi Folks!!!
I have a string let's say "hi" and got to reverse it using just a
single variable for swapping, how do I do it?
Regards,
Rajendra S.
|
by: Epson Barnett |
last post by:
Hi,
I'm new to C# and I'd like to be able to reference a field of an
object using a variable instead of literal text.
In the PHP scripting language, you can create a variable:
$var = "name";...
|
by: Jeff Higgins |
last post by:
I would like to use a string variable to declare a variable.
How would I accomplish this goal?
string nameMainMenu = "mnuMain";
string nameMenuItem = "mnuFile";
MainMenu {nameMainMenu} = new...
|
by: Blaxer |
last post by:
There is probably a really easy way to do this, so please forgive me but I would like to set the value of a variable from a variable, an example would be...
function Calculate_Something(ByVal...
|
by: Emilio |
last post by:
Question about
Shared Sub Connect(server As , message As )
Why is in square brackets?
Is it like
Shared Sub Connect(server() As String, message() As String)
|
by: yogi_bear_79 |
last post by:
I'm sure I have a few things wrong here. But I am stuck on how to do
a recurring search. Also my statement cin >quote; acts weird. If I
enter more than one word it blows right past cin >findMe;...
|
by: Slickuser |
last post by:
Hi,
I'm trying to use my variable in another variable. How can I achieve
that?
I can use + (concat) but it will be a lot me to concatenate.
I have to use 3 variables with 15 replacement.
...
|
by: selvialagar |
last post by:
Let me tell my problem.There are list of parameters in a list box. User can select any number of Parameters (Maximum Limit is 10). Now I want to create dynamic arrays for all the parameters....
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |