I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list boxes
do not necessarily need to have a selection made to be used in the
dynamic query.
In essence the form can have selections made in all or none of its list
boxes to form the dynamic query
I am looking to get some feedback in reference to my code. I am in no
way an expert and am always looking for some helpful hints to improve.
The following code is lenghty and complex and am sure can be condensed
and improved. If you got nothing else to do or are good natured enough
to help an inquiring mind I would appreciate some feedback.
Thanks in advance
*********begin code*********
'variable declaration
Dim varItem As Variant
Dim Query As String
Dim CriteriaProf As String
Dim CriteriaState As String
Dim CriteriaRPC As String
Dim CriteriaRegion As String
Dim CriteriaLanger As String
Dim chkEMail As String
' Set Criteria for Each ListBox
'1st set criteria with 'or' statement between each selection
'2nd trim the last 'or' statement off
'3rd put criteria in parentheses
For Each varItem In Listbox_Prof.ItemsSelected
CriteriaProf = CriteriaProf & " Profession='" &
Listbox_Prof.ItemData(varItem) & "' OR "
Next varItem
If Len(Nz(CriteriaProf)) <> 0 Then
CriteriaProf = Left(CriteriaProf, Len(CriteriaProf) - 3)
CriteriaProf = " ( " & CriteriaProf & " ) "
End If
For Each varItem In Listbox_State.ItemsSelected
CriteriaState = CriteriaState & " State='" &
Listbox_State.ItemData(varItem) & "' OR "
Next varItem
If Len(Nz(CriteriaState)) <> 0 Then
CriteriaState = Left(CriteriaState, Len(CriteriaState) - 3)
CriteriaState = " ( " & CriteriaState & " ) "
End If
For Each varItem In Listbox_RPC.ItemsSelected
CriteriaRPC = CriteriaRPC & " ReturnedPostCard=" &
Listbox_RPC.ItemData(varItem) & " OR "
Next varItem
If Len(Nz(CriteriaRPC)) <> 0 Then
CriteriaRPC = Left(CriteriaRPC, Len(CriteriaRPC) - 3)
CriteriaRPC = " ( " & CriteriaRPC & " ) "
End If
For Each varItem In Listbox_Region.ItemsSelected
CriteriaRegion = CriteriaRegion & " Region='" &
Listbox_Region.ItemData(varItem) & "' OR "
Next varItem
If Len(Nz(CriteriaRegion)) <> 0 Then
CriteriaRegion = Left(CriteriaRegion, Len(CriteriaRegion) - 3)
CriteriaRegion = " ( " & CriteriaRegion & " ) "
End If
'This ListBox holds three selection (N/A, Yes, No) for each slection
'specific criteria is set
For Each varItem In Listbox_Langer.ItemsSelected
CriteriaLanger = Listbox_Langer.ItemData(varItem)
Next varItem
If CriteriaLanger = "No" Then
CriteriaLanger = "( Heard<>'Langerman Lists' ) "
End If
If CriteriaLanger = "Yes" Then
CriteriaLanger = "( Heard='Langerman Lists' ) "
End If
If CriteriaLanger = "N/A" Then
CriteriaLanger = ""
End If
'Checkbox used to select a null value for criteria
chkEMail = Me.chkEmailOnly
If chkEMail = -1 Then
chkEMail = "( Email<>null ) "
End If
'Start to build dynamic SQL statement
'1st set SELECT statement
'2nd add WHERE clause for by adding criteria one at a time
'If Criteria for ListBox is not empty add it to SQL followed by
'AND' statement
'3rd Trim last 'And' statement
'qryDetailExport pre-existing with predefined fields needed for
output
Query = "SELECT * FROM qryDetailExport WHERE "
If Len(Nz(CriteriaProf)) > 0 Then
Query = Query & CriteriaProf & "AND "
End If
If Len(Nz(CriteriaState)) > 0 Then
Query = Query & CriteriaState & "AND "
End If
If Len(Nz(CriteriaRPC)) > 0 Then
Query = Query & CriteriaRPC & "AND "
End If
If Len(Nz(CriteriaRegion)) > 0 Then
Query = Query & CriteriaRegion & "AND "
End If
If Len(Nz(CriteriaLanger)) > 0 Then
Query = Query & CriteriaLanger & "AND "
End If
If Len(chkEMail) > 1 Then
Query = Query & chkEMail & "AND "
End If
Query = Left(Query, Len(Query) - 4)
'Create temp query with dynamic SQL statement to use for various output
reasons
Dim qryExport1 As New QueryDef
On Error Resume Next
CurrentDb.QueryDefs.delete ("qryExportTemp")
On Error GoTo 0 ' turn off error hand
qryExport1.Name = "qryExportTemp"
qryExport1.SQL = Query
CurrentDb.QueryDefs.Append qryExport1
CurrentDb.QueryDefs.Refresh
Me.CountTXT = DCount("*", "qryExportTemp")
*******end code***********