Report Generator

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

.

Relevant Pages

  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • Re: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Importing text, etc. on remote .mdb file
    ... You can then use VBA string functions to get from there to the ... Is there an easy way to schedule the imports of the text files so I ... 'Modify strSQL and filenames as required ... Dim oJet 'DAO.DBEngine ...
    (microsoft.public.access.externaldata)
  • Re: My code seems to hate me....
    ... The cmdSearch_Click procedure has no string variable named strSql. ... Dim strSELECT As String ... Private Sub butCancel_Click ...
    (microsoft.public.access.formscoding)