Iterating Variables

[oldozziesubby]

Hi, I'm hoping someone can help with this.

I've declared a number of variables ie. gintE1, gintE2, gintE3, etc

These variables hold a number, depending on a default setting. They might be 4 or 3 or 5

What I'm trying to do is use these variables to fill another variable
ie.
For x= 1 to 3
iNumber = gintE 'and then add the x to the end so that whatever is in the variable gintE1, ginteE2, gintE3 is put into iNumber
Next x

I know that it
For x= 1 to 3
iNumber = gintE & x
Next x

doesn't work, but I can't figure out how to do it.

Thank you

Peter

 

[Bodisathva]

assuming that the iterations of x (1,2,3...) are meant to represent gintE1, gintE2, gintE3... in the form gintEx, and that the eventual goal is the sum total of all the gintE values:

what you would do is create an array gintE then, after loading the appropriate numbers into the array, the function would be:

For x=1 to 3
     iNumber = iNumber + gintE(x)
Next x

 

[oldozziesubby]

Thanks Bodisathava, I'll try that

 

[oldozziesubby]

Hi Bodisathva

I used your suggestion about arrays and it worked...
The result is not elegant, but as I said, it works. - Thanks

If you are interested this is what I'm trying to do...

I'm writing a maintenance program and part of it is to transfer data from an equipment hierarchy of seven tables to an equipment details table. I've already sorted out how to do it from individual tables. But to do it in bulk, I've used the following code....

Option Compare Database
Option Explicit
Option Base 1

Public Function EquipmentTransfer(tblName As String)
Dim db As Database
Dim strSQL As String
Dim iLevel As Integer
Dim iLevelSize(7) As Integer
Dim x As Integer

gsGetDefaults
Set db = CurrentDb

iLevel = Right(tblName, 1)
For x = 1 To 7
If x = 1 Then iLevelSize(x) = gintE1
If x = 2 Then iLevelSize(x) = gintE2
If x = 3 Then iLevelSize(x) = gintE3
If x = 4 Then iLevelSize(x) = gintE4
If x = 5 Then iLevelSize(x) = gintE5
If x = 6 Then iLevelSize(x) = gintE6
If x = 7 Then iLevelSize(x) = gintE7
Next x

' Create First Query - qry_TransferEquipment
strSQL = "SELECT Mid(Location,4,50) & " & "'" & gstrSeparator & "' & "
For x = 1 To iLevel
strSQL = strSQL & "RightPad(" & "E" & x & "No" & ", ' '," & iLevelSize(x) & ")" & " & '" & gstrSeparator & "' & "
Next x
' Remove the last comma
strSQL = Left(strSQL, Len(strSQL) - 8)
strSQL = strSQL & "AS MaintLink"

For x = 1 To iLevel
strSQL = strSQL & ", " & "E" & x & "No"
Next x

strSQL = strSQL & ", E" & x - 1 & "Desc, Location"
strSQL = strSQL & " FROM " & tblName

If Not pfCreateQuery(db, "qry_TransferEquipment", strSQL) Then
MsgBox "Error Creating Query"
Exit Function
End If

' Create the Second Query - qry_TransferEquipment2
strSQL = "SELECT Location"
For x = 1 To iLevel
strSQL = strSQL & ", " & "E" & x & "No"
Next x
strSQL = strSQL & ", E" & x - 1 & "Desc, Date() AS EnterDate, fOSUserName() AS UID, MaintLink "
strSQL = strSQL & "FROM qry_TransferEquipment LEFT JOIN tbl_EquipmentDetails ON qry_TransferEquipment.MaintLink = tbl_EquipmentDetails.eqMaintLink "
strSQL = strSQL & "WHERE tbl_EquipmentDetails.eqID Is Null"

If Not pfCreateQuery(db, "qry_TransferEquipment2", strSQL) Then
MsgBox "Error Creating Query"
Exit Function
Else
' Run the equipment transfer code

End If

End Function

As I say it's not elegant but it works.

Thanks again for your help

Peter

 

[stopher]

For x = 1 To 7
If x = 1 Then iLevelSize(x) = gintE1
If x = 2 Then iLevelSize(x) = gintE2
If x = 3 Then iLevelSize(x) = gintE3
If x = 4 Then iLevelSize(x) = gintE4
If x = 5 Then iLevelSize(x) = gintE5
If x = 6 Then iLevelSize(x) = gintE6
If x = 7 Then iLevelSize(x) = gintE7
Next x

There's no need to iterate. This is better (7 steps instead of 49):

   iLevelSize(1) = gintE1
   iLevelSize(2) = gintE2
   iLevelSize(3) = gintE3
   iLevelSize(4) = gintE4
   iLevelSize(5) = gintE5
   iLevelSize(6) = gintE6
   iLevelSize(7) = gintE7

In fact, if gintE1-7 are global variables then why not replace them with iLevelSize() as a global.

 

[oldozziesubby]

Thanks stopher

In fact, if gintE1-7 are global variables then why not replace them with iLevelSize() as a global.

You are right.. I'm so stupid, why didn't I think of that?

In fact it is so bloody obvious, I'm ashamed I never thought of it... thanks again