## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# Set variable for a For Loop -

Posted on 2014-03-09
334 Views
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.
``````                     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

``````
0
Question by:Karen Schaefer
• 6
• 2
• 2
• +3

LVL 49

Assisted Solution

Gustav Brock earned 250 total points
ID: 39916082
Could be:

While rs.EOF = False
For nQtr = 1 To 6
For ntr = 1 To 6
i = "Q" & nQtr & "T" & ntr
If rs.Fields("Q" & CStr(i)).Value = True Then
' Do stuff.
End If
ntr = ntr + 1
Next
nQtr = nQtr + 1
Next
rs.MoveNext
Wend

/gustav
0

LVL 6

Expert Comment

ID: 39916087
Just debugging the logic in what you have there:
``````                       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
``````

should likely be
``````                       For nQtr = 1 To 6
For ntr = 1 To 6
i = "Q" & nQtr & "T" & ntr
If rs.Fields(CStr(i)).Value = True Then
``````

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).
0

LVL 33

Accepted Solution

Norie earned 250 total points
ID: 39916088
To refer to the fields you can use rs.Fields("Q" & nQtr & "T" & ntr), so the code would look something like this.
``````While rs.EOF = False
For nQtr = 1 To 5
For ntr = 1 To 5

strField = "Q" & nQtr & "T" & ntr

strNextField ="Q" & nQtr & "T" & ntr+1

If rs.Fields(strField).Value > rs.Fields(strNextField).Value Then

End If

Next ntr
Next nQtr

rs.MoveNext
Wend
``````

PS There is no need for CStr.
0

Author Comment

ID: 39916151
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
0

LVL 33

Expert Comment

ID: 39916156
If you notice in the code I posted the loop only goes to 5.
0

Author Comment

ID: 39916261
OH I see what you did, great thanks.
0

LVL 35

Expert Comment

ID: 39916393
with a properly normalized schema, you would do this with a query.

Where SomeDate Between Forms!yourform!StartDate AND Forms!yourform!EndDate;
0

LVL 45

Expert Comment

ID: 39916467
@kfschaefer1

You should add an Option Explicit statement in your General Declarations section.  The compiler would have caught the variable name spelling error.
0

Author Comment

ID: 39921485
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.
0

LVL 45

Expert Comment

ID: 39921486
@kfschaefer1

0

Author Closing Comment

ID: 39921522
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.
0

LVL 35

Expert Comment

ID: 39921549
When you're tired of writing code to deal with the unnormalized table, let us know and we will help to normalize it.
0

Author Comment

ID: 39921561
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.
0

Author Comment

ID: 39924234
I ended up creating a Union query to semi normalize the data, hoping this will make comparison a lot easier.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooksÂ® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of IntuitÂ®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain unâ€¦
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you toâ€¦
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦