Logic Help

[mark curtis]

Dear all,

I have a form with four independent list boxes on. A report is generated based on the criteria selected from one or more of the list boxes.

I use the code below to first build a string for each list box and then piece together all the strings into one string for my SQL string:

This works fine if I only select from the first list but I get an error if I select only from other lists. I know that the error is the AND gets caught up in the string but I don't know how to structure the code?



' build criteria string for selected Task Period.
For Each varItem In Me.lstTaskPeriod.ItemsSelected
sTaskPeriod = sTaskPeriod & ",'" & Me.lstTaskPeriod.ItemData(varItem) & "'"
Next varItem
sTaskPeriod = Mid(sTaskPeriod, 2) ' remove leading comma.
sTaskPeriod = "TaskPeriod in (" & sTaskPeriod & ")"
MsgBox (sTaskPeriod)

' build criteria string for selected Site.
For Each varItem In Me.lstSite.ItemsSelected
sSite = sSite & ",'" & Me.lstSite.ItemData(varItem) & "'"
Next varItem
sSite = Mid(sSite, 2) ' remove leading comma.
sSite = "Site in (" & sSite & ")"
MsgBox (sSite)

' build criteria string for selected AssetPosition.
For Each varItem In Me.lstassetPosition.ItemsSelected
sAssetPosition = sAssetPosition & ",'" & Me.lstassetPosition.ItemData(varItem) & "'"
Next varItem
sAssetPosition = Mid(sAssetPosition, 2) ' remove leading comma.
sAssetPosition = " AssetPosition in (" & sAssetPosition & ")"
MsgBox (sAssetPosition)

'' build criteria string for selected AssetNo.
For Each varItem In Me.lstAssetNo.ItemsSelected
sAssetNo = sAssetNo & ",'" & Me.lstAssetNo.ItemData(varItem) & "'"
Next varItem
sAssetNo = Mid(sAssetNo, 2) ' remove leading comma.
sAssetNo = " tblAssetTask.AssetNo in (" & sAssetNo & ")"
MsgBox (sAssetNo)

If Me.lstTaskPeriod.ItemsSelected.Count > 0 Then
sCriteria = sTaskPeriod
End If

If Me.lstSite.ItemsSelected.Count > 0 Then
sCriteria = sCriteria & " AND " & sSite
End If

If Me.lstassetPosition.ItemsSelected.Count > 0 Then
sCriteria = sCriteria & " AND " & sAssetPosition
End If

If Me.lstassetPosition.ItemsSelected.Count > 0 Then
sCriteria = sCriteria & " AND " & sAssetNo
End If

Thanks
Mark

 

[antomack]

The way you have written your if statements is not taking into account that a user may not have selected from one of the earlier list boxes. So you have to account for this, the easiest way being checking if sCriteria contains anything yet as done below.

sCriteria = ""

If Me.lstTaskPeriod.ItemsSelected.Count > 0 Then
  sCriteria = sTaskPeriod
End If

If Me.lstSite.ItemsSelected.Count > 0 Then
  If sCriteria = "" Then
    sCriteria = sSite
  Else
    sCriteria = sCriteria & " AND " & sSite
  End If
End If

If Me.lstassetPosition.ItemsSelected.Count > 0 Then
  If sCriteria = "" Then
    sCriteria = sAssetPosition
  Else
    sCriteria = sCriteria & " AND " & sAssetPosition
  End If
End If

If Me.lstassetPosition.ItemsSelected.Count > 0 Then
  If sCriteria = "" Then
    sCriteria = sAssetNo
  Else
    sCriteria = sCriteria & " AND " & sAssetNo
  End If
End If

 

[mark curtis]

Thanks for the help. A guy i work with suggested putting the answers in an array as follows:

'Array used to loop through listboxes and determine if items have been selected
If Me.lstTaskPeriod.ItemsSelected.Count > 0 Then sCriteriarr(0) = sTaskPeriod
If Me.lstSite.ItemsSelected.Count > 0 Then sCriteriarr(1) = sSite
If Me.lstassetPosition.ItemsSelected.Count > 0 Then sCriteriarr(2) = sAssetPosition
If Me.lstAssetNo.ItemsSelected.Count > 0 Then sCriteriarr(3) = sAssetNo

'For next loop to loop through above array and build up criteria string

For i = 0 To 3
If sCriteriarr(i) <> "" Then
If sCriteria <> "" Then
sCriteria = sCriteria & " AND " & sCriteriarr(i)
Else
sCriteria = sCriteriarr(i)
End If
End If
Next

Seems to be ok

Thanks
Mark