Noob VB code, Need help with Passing multiSelect Listbox

  • Thread starter Thread starter DeDBlanK
  • Start date Start date
D

DeDBlanK

I having trouble understanding what I am doing wrong here. I have
added to Mr. Browne's code to pull some Listboxes, with multiselect
enabled, passing it through the filter option on a report.
Issue is that it was working, but I kept getting errors passing
through the WHERE clause on the report. Now I can't get anything to
show up other that the original code information.
If someone could please help by pointing me in the correct direction,
I would be greatly appreciative.
Thanks

***********************************code*******************************
Private Sub cmdReport_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of
this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the
field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Dim varItemShift As Variant
Dim varItemDept As Variant
Dim strWhereShift As String
Dim strWhereDept As String
Dim strCriteria As String
Dim loqd As QueryDef
Dim strShiftField As String
Dim strDeptField As String

Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to
match your local settings.

'DO set the values in the next 3 lines.
strReport = "rptDTSum" 'Put your report name in these
quotes.
strDateField = "[dtmDate]" 'Put your field name in the square
brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead
of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format
(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'build filter string for Shift
strShiftField = "[strShift]"
If strWhereShift <> vbNullString Then
If strWhere <> vbNullString Then
With Me.lstShift
For Each varItemShift In .ItemsSelected
strWhereShift = strWhereShift & "(" &
strShiftField & " = '" & .ItemData(varItemShift) & "') OR "
Next varItemShift
End With
'Strip the " OR "
strWhereShift = " OR " & strWhereShift & Left
(strWhereShift, Len(strWhereShift) - 5)
Else
With Me.lstShift
For Each varItemShift In .ItemsSelected
strWhereShift = strWhereShift & strShiftField
& "= '" & .ItemData(varItemShift) & "' ,"
Next varItemShift
End With
'Strip the ", "
strWhereShift = strWhereShift & Left(strWhereShift, Len
(strWhereShift) - 2)
End If
End If

'build filter string for Dept
strDeptField = "[strResponsible]"
If strWhereDept <> vbNullString Then
If strWhere <> vbNullString Then
With Me.lstDept
For Each varItemDept In .ItemsSelected
strWhereDept = strWhereDept & strDeptField &
"= '" & .ItemData(varItemDept) & "') OR "
Next varItemDept
End With
strWhereDept = " OR " & strWhereDept & Left
(strWhereDept, Len(strWhereDept) - 5)
Else
With Me.lstDept 'if neither or null strip " OR "
For Each varItemDept In .ItemsSelected
strWhereDept = strWhereDept & strDeptField &
"= '" & .ItemData(varItemDept) & "' ,"
Next varItemDept
End With
'Strip the ", "
strWhereDept = strWhereDept & Left(strWhereDept, Len
(strWhereDept) - 2)
End If
End If


'Close the report if already open: otherwise it won't filter
properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print strWhereShift
Debug.Print strWhereDept
Debug.Print strWhere 'Remove the single quote from the
start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere &
strWhereShift & strWhereDept

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler

End Sub
*******************************end code*******************************
 
I having trouble understanding what I am doing wrong here.  I have
added to Mr. Browne's code to pull some Listboxes, with multiselect
enabled, passing it through the filter option on a report.
Issue is that it was working, but I kept getting errors passing
through the WHERE clause on the report.  Now I can't get anything to
show up other that the original code information.
If someone could please help by pointing me in the correct direction,
I would be greatly appreciative.
Thanks

***********************************code*******************************
Private Sub cmdReport_Click()
'On Error GoTo Err_Handler      'Remove the single quote from startof
this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation:http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" incase the
field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Dim varItemShift As Variant
    Dim varItemDept As Variant
    Dim strWhereShift As String
    Dim strWhereDept As String
    Dim strCriteria As String
    Dim loqd As QueryDef
    Dim strShiftField As String
    Dim strDeptField As String

        Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to
match your local settings.

    'DO set the values in the next 3 lines.
        strReport = "rptDTSum"      'Put your report namein these
quotes.
        strDateField = "[dtmDate]" 'Put your field name in the square
brackets in these quotes.
        lngView = acViewPreview     'Use acViewNormal to print instead
of preview.

    'Build the filter string.
        If IsDate(Me.txtStartDate) Then
            strWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
        End If
        If IsDate(Me.txtEndDate) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
        strWhere = strWhere & "(" & strDateField & " < " & Format
(Me.txtEndDate + 1, strcJetDate) & ")"
        End If

    'build filter string for Shift
    strShiftField = "[strShift]"
        If strWhereShift <> vbNullString Then
             If strWhere <> vbNullString Then
                With Me.lstShift
                    For Each varItemShift In .ItemsSelected
                        strWhereShift = strWhereShift & "(" &
strShiftField & " = '" & .ItemData(varItemShift) & "') OR "
                    Next varItemShift
                End With
                'Strip the " OR "
                strWhereShift = " OR " & strWhereShift & Left
(strWhereShift, Len(strWhereShift) - 5)
            Else
                With Me.lstShift
                    For Each varItemShift In .ItemsSelected
                        strWhereShift = strWhereShift & strShiftField
& "= '" & .ItemData(varItemShift) & "' ,"
                    Next varItemShift
                End With
                'Strip the ", "
                strWhereShift = strWhereShift & Left(strWhereShift, Len
(strWhereShift) - 2)
            End If
        End If

    'build filter string for Dept
    strDeptField = "[strResponsible]"
        If strWhereDept <> vbNullString Then
            If strWhere <> vbNullString Then
                With Me.lstDept
                    For Each varItemDept In .ItemsSelected
                        strWhereDept = strWhereDept & strDeptField &
"= '" & .ItemData(varItemDept) & "') OR "
                    Next varItemDept
                End With
                strWhereDept = " OR " & strWhereDept & Left
(strWhereDept, Len(strWhereDept) - 5)
            Else
                With Me.lstDept 'if neither or null strip" OR "
                    For Each varItemDept In .ItemsSelected
                        strWhereDept = strWhereDept & strDeptField &
"= '" & .ItemData(varItemDept) & "' ,"
                    Next varItemDept
                End With
                'Strip the ", "
                strWhereDept = strWhereDept & Left(strWhereDept, Len
(strWhereDept) - 2)
            End If
        End If

    'Close the report if already open: otherwise it won't filter
properly.
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If

    'Open the report.
    Debug.Print strWhereShift
    Debug.Print strWhereDept
    Debug.Print strWhere        'Remove the single quote fromthe
start of this line for debugging purposes.
        DoCmd.OpenReport strReport, lngView, , strWhere &
strWhereShift & strWhereDept

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler

End Sub
*******************************end code*******************************
Click to expand...

For anyone interested, I fixed it. Guess I just needed to take a
break and look at it again with a clear head.
What I did was changed from THIS = THAT to THAT IN (THESE ITEMS),
Changed the logic to look at the previous statement for NULL and then
added the " OR " to the beginning of the string.


**********************************CODE**********************************
'build filter string for Shift
strShiftField = "[strShift]"
strWhereShift = ""
With Me.lstShift
For Each varItemShift In .ItemsSelected
strWhereShift = strWhereShift & "'" & .ItemData
(varItemShift) & "',"
Next varItemShift
End With
'Strip the "," and " OR "
strWhereShift = strShiftField & " IN (" & Left(strWhereShift,
Len(strWhereShift) - 1) & ")"
If strWhere <> vbNullString Then
strWhereShift = " OR " & strWhereShift
End If

'build filter string for Dept
strDeptField = "[strResponsible]"
strWhereDept = ""
With Me.lstDept
For Each varItemDept In .ItemsSelected
strWhereDept = strWhereDept & "'" & .ItemData
(varItemDept) & "',"
Next varItemDept
End With
strWhereDept = strDeptField & " IN (" & Left(strWhereDept, Len
(strWhereDept) - 1) & ")"
If strWhereShift <> vbNullString Then
strWhereDept = " OR " & strWhereDept
End If

'Close the report if already open: otherwise it won't filter
properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
******************************END
CODE**************************************************
 
1.  Can you show us what is getting printed out in your immediate window as a
result of your debug.print statements at the bottom of your code?

2.  I have a function fnMultiList (shown below) that I use to concatenate the
selected values of multi-select listbox.  This function will return several
results that look like:
    a.  IS NOT NULL
    b.  "= 23"
    C.  IN (23, 34, 56)
Using this would significantly reduce the number of lines of code in this
routine.  I generally put this in a code module, so I can use it from
anywhere in my applications.

Public Function fnMultiList(lst As ListBox) As Variant

    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    Dim intBoundColumn As Integer

    fnMultiList = Null

    'Determine how to delimit the list items
    intBoundColumn = lst.BoundColumn - 1
    If IsNumeric(lst.Column(intBoundColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
    Elseif IsDate(lst.Column(intBoundColumn, Abs(lst.ColumnHeads))) Then
        strDelimeter = "#"
    Else
        strDelimiter = Chr$(34)
    End If

    'In the off chance that the list is not multi-select, use the lst..Value
property
    'loop through the selected items in the list
    If lst.MultiSelect = 0 Then
        fnMultiList = lst.Value
    Else
        For each varItem in lst.ItemsSelected
            'Using '+' to concatenate ensures no leading commas for first
item
            fnMultiList = (fnMultiList + ",") & strDelimiter _
                            & lst.Column(intBoundColumn, lngItem) &
strDelimiter
        Next varItem
    End If

    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len
(fnMultiList) - 1)

    'Depending on the number of items selected, determine how the "list
values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
    Else
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList, ",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList & ")"
        End Select
    End If

End Function

3.  I generally set my criteria variable (you used strWhere) as a Variant
data type, and set it to NULL to start with.  This allows me to avoid some of
the code that you have.  

I was a little confused by your code to build the Shift and Department
portions of the criteria because these both tested to see whether
strWhereShift or strWhereDept <> vbNullString, but since you never set these
values prior to the test, they were always going to be = vbNullString.
Additionally, in the section of code where you were trying to define the
Department criteria, you used:

DeptField = "[strResponsible]"

Do you really have a field named [strResponsible]?

I was also unsure what your error handler was checking for, so I left it
alone.

Private Sub cmdReport_Click()

    'Remove the single quote from start of next line once you have itworking.

    'On Error GoTo Err_Handler      
    'Purpose:       Filter a report to a date range.
    'Documentation:http://allenbrowne.com/casu-08.html
    'Note: Filter uses "less than the next day" in case the field hasa time
component.
    Dim strReport As String
    Dim strDateField As String
    Dim varWhere As Variant

    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    'DO set the values in the next 3 lines.
    strReport = "rptDTSum"      'Put your report name in these quotes.
    strDateField = "[dtmDate]" 'Put your field name in the square brackets in
these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
         varWhere = "(" & strDateField & " >= " & Format(Me..txtStartDate,
strcJetDate) & ")"
    End If

    'I like to use the DateValue function to strip times from date/time
fields
    If IsDate(Me.txtEndDate) Then
        varWhere = (varWhere + " AND ") _
                       & "(DateValue(" & strDateField & ")<= " & Format(Me.
txtEndDate, strcJetDate) & ")"
    End If

    'build filter string for Shift
    varWhere = (varWhere + " AND ") _
                   & "([ShiftField]" & fnMultiList(Me..lstShift) & ")"

    'build filter string for Dept
    varWhere = (varWhere + " AND ") _
                   & "([DeptField]" & fnMultiList(Me.lstDept) & ")"

    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    Debug.Print varWhere

    DoCmd.OpenReport strReport, lngView, , varWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
    End If
    Resume Exit_Handler

End Sub

HTH
Dale

--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200906/1
Click to expand...

Thanks for the help. I really appreciate the help.
I have tried the code and I am running into some issues.
If the user selects just the Ending Date, with the code supplied, it
generates:

AND (DateValue([dtmDate])<= #06/15/2009#) AND ([strShift] IS NOT NULL)
AND ([strResponsible] IS NOT NULL)

I need a condition stating if only the ending date is selected and
nothing selected in the end date. The original code I had used from
Allen Browne's site, I thought, did that. (http://allenbrowne.com/
casu-08.html)
I also noticed that the function sent, fnMultiList, is using bound
columns. Not sure if this would cause an issue, but each list box
that I am using has subquery to SELECT DISTINCT for the field. There
is only one column in the subquery. When selecting more than one
value, only the TOP value is repeating:
([strShift] IN ("A","A"))
When it should read:
([strShift] IN ("A","B"))
This also is happening with the lstDept.
Is there a way to set a condition in the function if only on Column
exists? I am guessing what's happening is that it's looking for the
column that doesn't exist and that's why I am getting just the first
value repeating.

Sorry I am such a noob.
 
1.  Can you show us what is getting printed out in your immediate window as a
result of your debug.print statements at the bottom of your code?
Click to expand...
2.  I have a function fnMultiList (shown below) that I use to concatenate the
selected values of multi-select listbox.  This function will return several
results that look like:
    a.  IS NOT NULL
    b.  "= 23"
    C.  IN (23, 34, 56)
Using this would significantly reduce the number of lines of code in this
routine.  I generally put this in a code module, so I can use it from
anywhere in my applications.
Click to expand...
Public Function fnMultiList(lst As ListBox) As Variant
Click to expand...
    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    Dim intBoundColumn As Integer
Click to expand...
    fnMultiList = Null
Click to expand...
    'Determine how to delimit the list items
    intBoundColumn = lst.BoundColumn - 1
    If IsNumeric(lst.Column(intBoundColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
    Elseif IsDate(lst.Column(intBoundColumn, Abs(lst.ColumnHeads)))Then
        strDelimeter = "#"
    Else
        strDelimiter = Chr$(34)
    End If
Click to expand...
    'In the off chance that the list is not multi-select, use the lst.Value
property
    'loop through the selected items in the list
    If lst.MultiSelect = 0 Then
        fnMultiList = lst.Value
    Else
        For each varItem in lst.ItemsSelected
            'Using '+' to concatenate ensures no leading commas for first
item
            fnMultiList = (fnMultiList + ",") & strDelimiter _
                            & lst.Column(intBoundColumn, lngItem) &
strDelimiter
        Next varItem
    End If
Click to expand...
    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len
(fnMultiList) - 1)
Click to expand...
    'Depending on the number of items selected, determine how the "list
values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
    Else
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList,",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList &")"
        End Select
    End If
Click to expand...
End Function
Click to expand...
3.  I generally set my criteria variable (you used strWhere) as a Variant
data type, and set it to NULL to start with.  This allows me to avoidsome of
the code that you have.  
Click to expand...
I was a little confused by your code to build the Shift and Department
portions of the criteria because these both tested to see whether
strWhereShift or strWhereDept <> vbNullString, but since you never set these
values prior to the test, they were always going to be = vbNullString..
Additionally, in the section of code where you were trying to define the
Department criteria, you used:
Click to expand...
DeptField = "[strResponsible]"
Click to expand...
Do you really have a field named [strResponsible]?
Click to expand...
I was also unsure what your error handler was checking for, so I left it
alone.
Click to expand...
Private Sub cmdReport_Click()
Click to expand...
    'Remove the single quote from start of next line once you have it working.
Click to expand...
    'On Error GoTo Err_Handler      
    'Purpose:       Filter a report to a date range.
    'Documentation:http://allenbrowne.com/casu-08.html
    'Note: Filter uses "less than the next day" in case the field has a time
component.
    Dim strReport As String
    Dim strDateField As String
    Dim varWhere As Variant
Click to expand...
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
Click to expand...
    'DO set the values in the next 3 lines.
    strReport = "rptDTSum"      'Put your report name in these quotes.
    strDateField = "[dtmDate]" 'Put your field name in the squarebrackets in
these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
Click to expand...
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
         varWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate,
strcJetDate) & ")"
    End If
Click to expand...
    'I like to use the DateValue function to strip times from date/time
fields
    If IsDate(Me.txtEndDate) Then
        varWhere = (varWhere + " AND ") _
                       & "(DateValue(" & strDateField & ")<= " & Format(Me.
txtEndDate, strcJetDate) & ")"
    End If
Click to expand...
    'build filter string for Shift
    varWhere = (varWhere + " AND ") _
                   & "([ShiftField]" & fnMultiList(Me.lstShift) & ")"
Click to expand...
    'build filter string for Dept
    varWhere = (varWhere + " AND ") _
                   & "([DeptField]" & fnMultiList(Me.lstDept) & ")"
Click to expand...
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
Click to expand...
    'Open the report.
    Debug.Print varWhere
Click to expand...
    DoCmd.OpenReport strReport, lngView, , varWhere
Click to expand...
Exit_Handler:
    Exit Sub
Click to expand...
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,
"Cannot open report"
    End If
    Resume Exit_Handler
Click to expand...
End Sub

Dale Fye
Click to expand...
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200906/1
Click to expand...

Thanks for the help.  I really appreciate the help.
I have tried the code and I am running into some issues.
If the user selects just the Ending Date, with the code supplied, it
generates:

AND (DateValue([dtmDate])<= #06/15/2009#) AND ([strShift] IS NOT NULL)
AND ([strResponsible] IS NOT NULL)

I need a condition stating if only the ending date is selected and
nothing selected in the end date.  The original code I had used from
Allen Browne's site, I thought, did that.  (http://allenbrowne.com/
casu-08.html)
I also noticed that the function sent, fnMultiList, is using bound
columns.  Not sure if this would cause an issue, but each list box
that I am using has subquery to SELECT DISTINCT for the field.  There
is only one column in the subquery. When selecting more than one
value, only the TOP value is repeating:
([strShift] IN ("A","A"))
When it should read:
([strShift] IN ("A","B"))
This also is happening with the lstDept.
Is there a way to set a condition in the function if only on Column
exists?  I am guessing what's happening is that it's looking for the
column that doesn't exist and that's why I am getting just the first
value repeating.

Sorry I am such a noob.- Hide quoted text -

- Show quoted text -
Click to expand...

I believe I got the date parameter working with:
varWhere = Null 'Set to null to check later if value has been
assigned

'Build the filter string.
If IsDate(Me.txtStartDate) Then
varWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
End If

'I like to use the DateValue function to strip times from date/
time Fields
If IsDate(Me.txtEndDate) Then
If varWhere <> vbNullString Then 'Check if start date had a
value, if so add AND and end date
varWhere = (varWhere + " AND ") & "(DateValue(" &
strDateField & ")<= " & Format(Me.txtEndDate, strcJetDate) & ")"
Else ' just add end date
varWhere = "(DateValue(" & strDateField & ")<= " & Format
(Me.txtEndDate, strcJetDate) & ")"
End If
End If
 
No need to be sorry about being a NOOB (we were all one once)!

1.  Add a line right before the start date code.  It should look like:

   'Build the filter string.
   varWhere = NULL
   If IsDate(Me.txtStartDate) Then

2.  Sorry, I made a couple of changes to fnMultiSelect in the version Isent
you.  You need to replace lngItem with varItem in the line that reads:

& lst.Column(intBoundColumn, lngItem) &

3. As you noticed, if no item in the list is selected, the version I passed
you returns a string that looks like

[FieldName] IS NOT NULL

If you don't want this, then you could do something like:

  'build filter string for Shift
   if me.lstShift.ItemsSelected >= 1 then
       varWhere = (varWhere + " AND ") _
                      & "([ShiftField]" & fnMultiList(Me.lstShift) & ")"
   End If

This would bypass that portion of the criteria string all together.

HTH
Dale




1.  Can you show us what is getting printed out in your immediate window as a
result of your debug.print statements at the bottom of your code?
Click to expand...
[quoted text clipped - 154 lines]
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200906/1
Click to expand...
Click to expand...
Thanks for the help.  I really appreciate the help.
I have tried the code and I am running into some issues.
If the user selects just the Ending Date, with the code supplied, it
generates:
Click to expand...
AND (DateValue([dtmDate])<= #06/15/2009#) AND ([strShift] IS NOT NULL)
AND ([strResponsible] IS NOT NULL)
Click to expand...
I need a condition stating if only the ending date is selected and
nothing selected in the end date.  The original code I had used from
Allen Browne's site, I thought, did that.  (http://allenbrowne.com/
casu-08.html)
I also noticed that the function sent, fnMultiList, is using bound
columns.  Not sure if this would cause an issue, but each list box
that I am using has subquery to SELECT DISTINCT for the field.  There
is only one column in the subquery. When selecting more than one
value, only the TOP value is repeating:
([strShift] IN ("A","A"))
When it should read:
([strShift] IN ("A","B"))
This also is happening with the lstDept.
Is there a way to set a condition in the function if only on Column
exists?  I am guessing what's happening is that it's looking for the
column that doesn't exist and that's why I am getting just the first
value repeating.
Click to expand...
Sorry I am such a noob.
Click to expand...

--
HTH

Dale Fye

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
Click to expand...

Almost everything is working. Just a one more thing. The bypass for
IS NOT NULL that you suggested is throwing a 'Argument Not Optional'
I have have no idea what's causing it.
 
If you set varWhere = NULL then you really should not need all of the
additional code.

There are two ways to concatenate values together.

1.  Use the "+" symbol.  With this method a variable that is NULL when
concatenated to any other value will always return a NULL.
2.  Use the "&" symbol.  With this method a variable that is NULL when
concatenated to any other value will always return the other value.

So, the equation:

varWHERE = (varWHERE + " AND ") & "([SomeField] = -1)"

will evaluate the portion in parenthesis first (varWhere + " AND ").  If
varWhere is NULL, then the value inside the ( ) will evaluate to NULL, which
makes the whole line look like:

varWhere = NULL & "([SomeField] = -1)"

Which evaluates to:

varWhere = "([SomeField] = -1)"

HTH
Dale




On Jun 17, 9:16 am, "Dale_Fye via AccessMonster.com" <u43991@uwe>
wrote:
Click to expand...
[quoted text clipped - 187 lines]
- Show quoted text -
Click to expand...
Click to expand...
I believe I got the date parameter working with:
    varWhere = Null  'Set to null to check later if value has been
assigned
Click to expand...
    'Build the filter string.
   If IsDate(Me.txtStartDate) Then
        varWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
   End If
Click to expand...
   'I like to use the DateValue function to strip times from date/
time Fields
   If IsDate(Me.txtEndDate) Then
       If varWhere <> vbNullString Then  'Check if start datehad a
value, if so add AND and end date
           varWhere = (varWhere + " AND ") & "(DateValue(" &
strDateField & ")<= " & Format(Me.txtEndDate, strcJetDate) & ")"
       Else  ' just add end date
           varWhere = "(DateValue(" & strDateField & ")<= " & Format
(Me.txtEndDate, strcJetDate) & ")"
       End If
   End If
Click to expand...

--
HTH

Dale Fye

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
Click to expand...

I guess I should of been clearer on what the issue was.

'build filter string for Dept
If Me.lstDept.ItemsSelected >= 1 Then
varWhere = (varWhere + " AND ") & "([strResponsible]" &
fnMultiList(Me.lstDept) & ")"
End If

This is generating the 'Argument Not Optional' when executed.
If I comment out the 'if' it works (of course then it process the IS
NOT NULL in the WHERE)
 
Should read:

 if me.lstShift.ItemsSelected.Count >= 1 then

Dale
[quoted text clipped - 71 lines]
- Show quoted text -
Click to expand...
Click to expand...
Almost everything is working.  Just a one more thing.  The bypass for
IS NOT NULL that you suggested is throwing a 'Argument Not Optional'
I have have no idea what's causing it.
Click to expand...

--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200906/1
Click to expand...

Works like a charm.

Thank you for all you great help!!! Most people just give solutions
and don't explain why. Thank you for your time!

Here is the final code:
*******************************CODE
START*******************************************
Private Sub cmdReport_Click()

'Remove the single quote from start of next line once you have it
working.
On Error GoTo Err_Handler
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a
time component.
Dim strReport As String
Dim strDateField As String
Dim varWhere As Variant

Const strcJetDate = "\#mm\/dd\/yyyy\#"


'DO set the values in the next 3 lines.
strReport = "rptDTSum" 'Put your report name in these quotes.
strDateField = "[dtmDate]" 'Put your field name in the square
brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of
preview.
varWhere = Null 'Set to null to check later if value
has been assigned

'Build the filter string.
If IsDate(Me.txtStartDate) Then
varWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
End If

'I like to use the DateValue function to strip times from date/
time Fields
If IsDate(Me.txtEndDate) Then 'Check if start date had a value,
if so add AND and end date
If varWhere <> vbNullString Then
varWhere = (varWhere + " AND ") & "(DateValue(" &
strDateField & ")<= " & Format(Me.txtEndDate, strcJetDate) & ")"
Else ' just add end date
varWhere = "(DateValue(" & strDateField & ")<= " & Format
(Me.txtEndDate, strcJetDate) & ")"
End If
End If

'build filter string for Shift
varWhere = (varWhere + " AND ") & "([strShift]" & fnMultiList
(Me.lstShift) & ")"


'build filter string for Dept
If Me.lstDept.ItemsSelected.Count >= 1 Then
varWhere = (varWhere + " AND ") & "([strResponsible]" &
fnMultiList(Me.lstDept) & ")"
End If

'Close the report if already open: otherwise it won't filter
properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print varWhere


DoCmd.OpenReport strReport, lngView, , varWhere


Exit_Handler:
Exit Sub


Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler


End Sub
******************************************CODE
END**********************************
 
Back
Top