Report Generator

[Intrepid_Yellow]

Hi,

I have created a basic report genertor, the user can choose a table (from
cboTable), then fields from that table (from lstTableFields) and the select a
specific value from one of those fields (select field from cboFieldName1 then
a value from lstFieldValues1).

Everything works well. So now the issue... I have added a cboFieldName2, 3 &
4 and a lstFieldValues2, 3 & 4. What is the best way to rework the code below
so that the report reflects all conditions the user selects (anywhere from
0-4)???

' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues1.ListCount - 1
If lstFieldValues1.Selected(i) Then
If lstFieldValues1.Column(0, i) = "All" Then
flgAll = True
End If

' checks data type of field for delimiting
If cboFieldName1.Column(1) >= 1 And cboFieldName1.Column(1) <= 7
Then
' if the field holds numeric data
strWhereIN = strWhereIN & " " & lstFieldValues1.Column(0, i)
& ","
ElseIf cboFieldName1.Column(1) = 8 Then
' if the field is a date
strWhereIN = strWhereIN & "#" & lstFieldValues1.Column(0, i)
& "#,"
ElseIf cboFieldName1.Column(1) = 10 Then
'if the field is text
strWhereIN = strWhereIN & "'" & lstFieldValues1.Column(0, i)
& "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the -IN-
string
strWhere = " WHERE " & Me!cboFieldName1 & " in (" & Left(strWhereIN,
Len(strWhereIN) - 1) & ")"
'MsgBox strWhere
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If
'MsgBox strSQL

All replies are greatly appreciated