?
Solved

Set variable for a For Loop -

Posted on 2014-03-09
14
Medium Priority
?
338 Views
Last Modified: 2014-03-12
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
 

Open in new window

0
Comment
Question by:Karen Schaefer
[X]
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
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +3
14 Comments
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 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

by:slinkygn
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

Open in new window


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

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

Accepted Solution

by:
Norie earned 1000 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

Open in new window


PS There is no need for CStr.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:Karen Schaefer
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 34

Expert Comment

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

Author Comment

by:Karen Schaefer
ID: 39916261
OH I see what you did, great thanks.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39916393
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;
0
 
LVL 46

Expert Comment

by:aikimark
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

by:Karen Schaefer
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 46

Expert Comment

by:aikimark
ID: 39921486
@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.
0
 

Author Closing Comment

by:Karen Schaefer
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 38

Expert Comment

by:PatHartman
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

by:Karen Schaefer
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

by:Karen Schaefer
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question