FOR NEXT LOOPS

[mrwendell]

Before you laugh...please read :0)

I have a form...3 textboxes...cmdbtn. What i what to do is input the the default value in the txtboxes...click continue and generate a report or file with every possible combination beginning with the default.

so box1 default is always..."S"
box2 can be alpha/numeric (A-m/p-Z and 1-9)no zero or "O"
box3 is same as box2

so my starting point is S1A I want the increment by 1...box2 & box3....BUT once they reach the max 9 or Z it should jump to either the alpha or numeric....

so S1A reaches S1Z...it should increment to S2A....

can anyone show me how?

now you can laugh....:0)

 

[Spellman]

try something like this:

Dim startchar as long

for i = 0 to 9
for j = 0 to 25

startchar = ASCII('A')
debug.print text1 + str(i) + chr(startchar)
startchar = startchar + 1

Next j
Next i

Mark

The key to immortality is to make a big impression in this life!!

 

[zemp]

Try this, it is based on what Spellman posted but uses the Ascii character set values for the letters.

For i = 1 To 9
     For j = 65 To 90
         Debug.Print "S" + CStr(i) + Chr$(j)
     Next j
   Next i

You can replace the had coded "S" with a variable or control if you need to.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[mrwendell]

thanks fellas....spellman, It didnt like the startchar = ASCII('A') but the second code worked fine. however, how would you loop thru from numeric to alpha in both boxes once it reaches 9 or Z....ie. S9Z is the last loop...how do we get to SA1 and eventually to SAA

 

[zemp]

You will need to switch you loop combinations a bit. For SA1 tp SZ9 try,

For i = 65 To 90
  If i <> 79 then
    For j = 1 To 9
        Debug.Print &quot;S&quot; + Chr$(i) + CStr(j)
    Next j
  End If
Next i

For SAA to SZZ try

For i = 65 To 90
  If i <> 79 then
    For j = 65 To 90
        If j <> 79 Then Debug.Print &quot;S&quot; + Chr$(i) + Chr$(j)
    Next j
  End If
Next i

etc., etc.

The if statements skips the letter 'O'.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[mrwendell]

great it ran....but i need to somehow put an if statement in to kick to the other loop, if i am reading you correctly, i am gonna need 3 or 4 fornext loops. OR enclose the all the loops in an IF statement. on the Debug.Print "S" + Chr$(i) + CStr(j)...i get an error if i try to place this in a variable...(s1=debug.print)....if i can get that to a variable then i could run an if statement on it...right? another issue....i can only see my results in the immediate window. to get it to a report...should i put a docmd.print below the debug.print...or simply replace it with a docmd.print

 

[zemp]

I think you got it. You will need to keep track of where you are and which loop you will need next.

The debug.print is just an easy way for you to see the results. You can replace it with almost anything. A variable, control, send it to the printer or a files, etc, etc.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."