Karen Schaefer
asked on
Set variable for a For Loop -
I need to loop thru a series of data fields and be able to change the number with in the field name.
is. Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6,
Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6,
Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6,
Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6,
Q5T1, Q5T2, Q5T3.Q5T4, Q5T5, Q5T6,
Q6T1, Q6T2, Q6T3,Q6T4, Q6T5, Q6T6,
Total of 36 fields that I will need to be able to capture the value so that I may compare it to another value to see which is greater.
So I want to simplify this by using a variable for the 2nd position and 4 position. What is the best solution.
I will need to be able to increment the numbers to compare the value of
Q1T1 > Q1T2
Hence the need to Q1T & (i) + 1 = Q1T2, etc.
This is my first attempt.
is. Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6,
Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6,
Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6,
Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6,
Q5T1, Q5T2, Q5T3.Q5T4, Q5T5, Q5T6,
Q6T1, Q6T2, Q6T3,Q6T4, Q6T5, Q6T6,
Total of 36 fields that I will need to be able to capture the value so that I may compare it to another value to see which is greater.
So I want to simplify this by using a variable for the 2nd position and 4 position. What is the best solution.
I will need to be able to increment the numbers to compare the value of
Q1T1 > Q1T2
Hence the need to Q1T & (i) + 1 = Q1T2, etc.
This is my first attempt.
While rs.EOF = False
For nQtr = 1 To 6
For ntr = 1 To 6
i = "Q" & nCt & "T" & ntr
n = 1 + 1
If rs.Fields("Q" & CStr(i)).Value = True Then
End If
Next
rs.MoveNext
Wend
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok all are great examples, Thanks, however, once the ntr value = 6 then I do not want to be able to increment to 7.
What do I need to do to the "strNextField ="Q" & nQtr & "T" & ntr+1' to prevent the incrementation?
K
What do I need to do to the "strNextField ="Q" & nQtr & "T" & ntr+1' to prevent the incrementation?
K
If you notice in the code I posted the loop only goes to 5.
ASKER
OH I see what you did, great thanks.
with a properly normalized schema, you would do this with a query.
Select Max(yourField) From your table
Where SomeDate Between Forms!yourform!StartDate AND Forms!yourform!EndDate;
Select Max(yourField) From your table
Where SomeDate Between Forms!yourform!StartDate AND Forms!yourform!EndDate;
@kfschaefer1
You should add an Option Explicit statement in your General Declarations section. The compiler would have caught the variable name spelling error.
You should add an Option Explicit statement in your General Declarations section. The compiler would have caught the variable name spelling error.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for kfschaefer1's comment #a39916151
for the following reason:
thanks for the great assist.
Accepted answer: 0 points for kfschaefer1's comment #a39916151
for the following reason:
thanks for the great assist.
@kfschaefer1
Your comment does not appear to be the one that has answered your question. I have interrupted your close request (by objection). Please accept one or more of the experts comments. If I am wrong in my assertion, please explain how your comment IS an answer to your question.
Your comment does not appear to be the one that has answered your question. I have interrupted your close request (by objection). Please accept one or more of the experts comments. If I am wrong in my assertion, please explain how your comment IS an answer to your question.
ASKER
Thanks these are the changes I needed thanks for your input. Note; to the statement about the Option Explicit. I just didn't include it in the value I posted, but always use it. Thanks. for the comment.
When you're tired of writing code to deal with the unnormalized table, let us know and we will help to normalize it.
ASKER
Unfortunately, I am working a project, where that is just not the case, and i am wrapping the project up. I inherited the project, and It is a real mess. but hire only to fix a couple of reports, not the entire database. Thanks for your input.
ASKER
I ended up creating a Union query to semi normalize the data, hoping this will make comparison a lot easier.
Open in new window
should likely be
Open in new window
And then you can just reuse nQtr and ntr in the body of the If block itself adding whatever number is appropriate to get to the field to compare to (can't give much more detail than that, just depends on how you're trying to compare all the fields).