increment variable in for loop

[kscheu via AccessMonster.com]

How do I increment a variable field using a counter in a for loop

dim strsql as string
dim strsql1, strsql2, strsql3,strsql4,strsql5,strsql6 as string

' I have 6 filters and I am trying to create a sql string using a loop

strsql = "Select * from query where "
strsql1 = " [div] = " & Me.cboDiv
strsql2 = " [order] = " & Me.cboOrder
..
..
..
For intcnt = 1 To 6
If strsql&intcnt <> ""
strsql = strsql & strsql&intcnt
End If
Next

My sql should look like: Select * from query where [div] = " & Me.cboDiv and
[order] = " & Me.cboOrder

How can I do this using the for loop?
Thanks.

 

[Guest]

You do not have to increment a variable for a For Next loop. It does that
for your.
This line:
For intcnt = 1 To 6
will control that. intcnt will have a value of 1 the first time through the
loop. the last time, the value will be 6. The To six means execute while
intcnt is <= 6

Now, you don't really need a loop for this. The way you have it coded, it
will error out. You cannot concatenate values to reference a variable name.
All you really need is:

strsql = "Select * from query where [div] = '" & Me.cboDiv & "' And [order]
= '" _
& Me.cboOrder & "';"

If, what you are trying to do is select some filters, but not all, that is
an entirely different and more difficult matter. If this is the case, post
back with more info, like how you are selecting which fields to filter on.

 

[kscheu via AccessMonster.com]

Yes, I am trying to select some filters. Sorry if I was not clear about that.
I have a main form with two sub forms. The first sub form is linked to the
second sub form. The main form contains 6 combo boxes that allow the user to
select filters. The after update property of each combo box currently sets
the recordsource of the first subform. This works fine if a user only wants
to select one filter at a time, but I want them to be able to select all of
them if they want. I thought that I could call a buildfilter function that
would be called on the after update event of all combo boxes. This function
would check the value of the combo boxes and build the sql. In order to do
this, I thought I could the criteria for each of the filters to a variable
such as strsql = " [div] = '" & Me.cboDiv , strsql2 = [order] = '" & Me.
cboOrder, strsql3 = "[cust] = " & me.cbocust , etc.
If the combo box was not selected for filter, the strsql1 variable will be "",
and so forth.

Then I would in a for loop, check the value of strsql&intcnt
For intcnt = 1 to 6
If strsql&intcnt <> ""
Also, if the filter count = 1 then
strsql = strsql & strsql&intcnt
else
strsql = strsql & " AND " & strsql&intcnt
end if

Are there any better ways of doing this?

Thanks.

You do not have to increment a variable for a For Next loop. It does that
for your.
This line:
For intcnt = 1 To 6
will control that. intcnt will have a value of 1 the first time through the
loop. the last time, the value will be 6. The To six means execute while
intcnt is <= 6

Now, you don't really need a loop for this. The way you have it coded, it
will error out. You cannot concatenate values to reference a variable name.
All you really need is:

strsql = "Select * from query where [div] = '" & Me.cboDiv & "' And [order]
= '" _
& Me.cboOrder & "';"

If, what you are trying to do is select some filters, but not all, that is
an entirely different and more difficult matter. If this is the case, post
back with more info, like how you are selecting which fields to filter on.

How do I increment a variable field using a counter in a for loop

[quoted text clipped - 20 lines]

How can I do this using the for loop?
Thanks.

 

[Guest]

I don' know that a For Next loop is what you want in this case. It would
work if you had naming for your combo boxes where they are all named the same
except a number at the end. For example cboFilter1, cboFilter2, etc. Then
you could use a for next and address them like:

dim varFields As Variant

Array varFields("[SomeField = ] ", "[AnotherField] = ", "[Field3] =
",....)

For intCtr = 1 to 6
If Not IsNull Me.Controls("cboFilter" & Cstr(intCtr) Then
strFilter = strFilter & varFields(intCtr) & "'" &
Me.Controls("cboFilter" _
& Cstr(intCtr) & "' And "
End If
Next intCtr
if strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 5) ' Take on the last And
End If

The above is not tested or complete, but should give you an idea of how it
could work. In your case, it may be just as easy to do each one individually.

If Not IsNull(Me.cboDiv) Then
strFilter = "[div] = '" & Me.cboDiv & "' And "
End If
If Not IsNull(Me.Order) Then
strFilter = "[order] = '" & Me.cboOrder & "' And "
End If

etc.

Yes, I am trying to select some filters. Sorry if I was not clear about that.
I have a main form with two sub forms. The first sub form is linked to the
second sub form. The main form contains 6 combo boxes that allow the user to
select filters. The after update property of each combo box currently sets
the recordsource of the first subform. This works fine if a user only wants
to select one filter at a time, but I want them to be able to select all of
them if they want. I thought that I could call a buildfilter function that
would be called on the after update event of all combo boxes. This function
would check the value of the combo boxes and build the sql. In order to do
this, I thought I could the criteria for each of the filters to a variable
such as strsql = " [div] = '" & Me.cboDiv , strsql2 = [order] = '" & Me.
cboOrder, strsql3 = "[cust] = " & me.cbocust , etc.
If the combo box was not selected for filter, the strsql1 variable will be "",
and so forth.

Then I would in a for loop, check the value of strsql&intcnt
For intcnt = 1 to 6
If strsql&intcnt <> ""
Also, if the filter count = 1 then
strsql = strsql & strsql&intcnt
else
strsql = strsql & " AND " & strsql&intcnt
end if

Are there any better ways of doing this?

Thanks.

You do not have to increment a variable for a For Next loop. It does that
for your.
This line:
For intcnt = 1 To 6
will control that. intcnt will have a value of 1 the first time through the
loop. the last time, the value will be 6. The To six means execute while
intcnt is <= 6

Now, you don't really need a loop for this. The way you have it coded, it
will error out. You cannot concatenate values to reference a variable name.
All you really need is:

strsql = "Select * from query where [div] = '" & Me.cboDiv & "' And [order]
= '" _
& Me.cboOrder & "';"

If, what you are trying to do is select some filters, but not all, that is
an entirely different and more difficult matter. If this is the case, post
back with more info, like how you are selecting which fields to filter on.

How do I increment a variable field using a counter in a for loop

[quoted text clipped - 20 lines]

How can I do this using the for loop?
Thanks.

 

[kscheu via AccessMonster.com]

Thank you so much for your help. Your right. Sometimes its better to keep
it simple.

I don' know that a For Next loop is what you want in this case. It would
work if you had naming for your combo boxes where they are all named the same
except a number at the end. For example cboFilter1, cboFilter2, etc. Then
you could use a for next and address them like:

dim varFields As Variant

Array varFields("[SomeField = ] ", "[AnotherField] = ", "[Field3] =
",....)

For intCtr = 1 to 6
If Not IsNull Me.Controls("cboFilter" & Cstr(intCtr) Then
strFilter = strFilter & varFields(intCtr) & "'" &
Me.Controls("cboFilter" _
& Cstr(intCtr) & "' And "
End If
Next intCtr
if strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 5) ' Take on the last And
End If

The above is not tested or complete, but should give you an idea of how it
could work. In your case, it may be just as easy to do each one individually.

If Not IsNull(Me.cboDiv) Then
strFilter = "[div] = '" & Me.cboDiv & "' And "
End If
If Not IsNull(Me.Order) Then
strFilter = "[order] = '" & Me.cboOrder & "' And "
End If

etc.

Yes, I am trying to select some filters. Sorry if I was not clear about that.
I have a main form with two sub forms. The first sub form is linked to the

[quoted text clipped - 50 lines]