code to display or not display text box control on report

[THWatson]

Using Access 2000

On a form, there is a multi-select list box. The Row Source for the list box is...

SELECT CustomerID,FullName, ProjectNbr, Format(Balance,"currency") AS [Balance Owing], DateDiff("d",[CompletionDateActual],Date()) AS [Days Outstanding] FROM qryBalance WHERE (((qryBalance.Balance)>0)) UNION SELECT "0","<ALL>",'','','' FROM qryBalance
ORDER BY FullName;

Behind the Preview button is the following code...

Private Sub cmdStatement_Click()
On Error GoTo Err_cmdStatement_Click

Dim ndx As Integer
Dim strList As String
Dim strWHERE As String
Dim stDocName As String

stDocName = "rptStatement"

'Default where-condition is empty
strWHERE = ""

'Test if "<ALL>" is selected
If Me.lstBalance.Selected(0) = False Then
'Loop through items
For ndx = 0 To Me.lstBalance.ListCount - 1
If Me.lstBalance.Selected(ndx) = True Then
strList = strList & Me.lstBalance.ItemData(ndx) & ", "
End If
Next ndx
' Get out if nothing selected
If strList = "" Then
MsgBox "Please select one or more items.", vbExclamation
Me.lstBalance.SetFocus
Exit Sub
End If
strList = Left(strList, Len(strList) - 2)
strWHERE = "[CustomerID] IN (" & strList & ")"
End If

DoCmd.OpenReport stDocName, acPreview, , strWHERE

For ndx = 0 To Me.lstBalance.ListCount - 1
Me.lstBalance.Selected(ndx) = False
Next
Me.txtSelected = Null
Me.Text19 = Null

Exit_cmdStatement_Click:
Exit Sub

Err_cmdStatement_Click:
MsgBox Err.Description
Resume Exit_cmdStatement_Click

End Sub

In the ProjectID Group Footer is a control called [txtOwing] for which the Control Source is...

="Days since invoiced: " & DateDiff("d",[CompletionDateActual],Date()) & "
Please remit outstanding balance. Thanks!"

On the OnFormat event for the ProjectID Group Footer is the following code, to display or not display [txtOwing] depending on the DateDiff value...

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Dim daysTemp As Integer
daysTemp = DateDiff("d", [CompletionDateActual], Date)
Me.txtOwing.Visible = (daysTemp >= 30)
End Sub

All of this works fine, no matter how many selections are made in the list box...UNLESS you select <All>. If <All> is selected the code faults out with an Invalid use of Null error on the line
daysTemp = DateDiff("d", [CompletionDateActual], Date)

Any suggestions as to how I fix this would be appreciated.

Thanks.

Tom

 

[dhookom]

Is it possible that CompletionDateActual might be null? If so, try:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Dim daysTemp As Integer
    If not IsNull(Me.CompletionDateActual) Then
        daysTemp = DateDiff("d", [CompletionDateActual], Date)
     Else
        daysTemp = 0
    End If
    Me.txtOwing.Visible = (daysTemp >= 30)
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Actually, the CompletionDateActual will never be null as this is always filled in at the time of Invoice. Therefore, providing one makes selections on the rows in the list box, data will always be present, and CompletionDateActual is Column(4) in the SQL behind the list box.

The problem seems to be that if one selects <ALL> Column(4) has no value...in fact, the only column to have value is Column(0)...and this produces the "invalid use of Null" error.

Tom

 

[THWatson]

Duane
I should add that using the code you suggested does not solve the problem. It results in a report with no data except for the customer name.

Tom

 

[dhookom]

I try to use a generic multi-select list box code where having none selected is the same as ALL.

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, _
        strDataType As String) As String
    'send in a list box control object
    'strFieldName is the field to filter on
    'strDataType is either T-Text, N-Numeric, or D-Date
    Dim strIn As String
    Dim varItem As Variant
    Dim strDelim   'used for delimiter
    'Set the delimiter used in the IN (...) clause
    Select Case strDataType
        Case "T"  'text data type
            strDelim = "'"  'double quote
        Case "N"  'numeric data type
            strDelim = ""
        Case "D"  'Date data type
            strDelim = "#"
    End Select
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " [" & strFieldName & "] In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
     Else
        'send back nothing
        strIn = " 1=1 "
    End If
    BuildIn = strIn

End Function

I don't see any reference to Column(4) in your code.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I haven't tried your list box formulation yet, but in reply to your question about a reference to Column(4) in the OnFormat code for the ProjectID footer, I have tried...

Dim daysTemp As Variant
daysTemp = Forms!frmCustomers!lstBalance.Column(4)
Me.txtOwing.Visible = (daysTemp >= 30)

This also works fine unless one selects <ALL>.

What I am trying to do is be able to issue statements of account to those customers in the list who have balances outstanding 30 days or more...or also be able to issue monthly statements to any customer who has a balance, but only display the "outstanding" text box if the balance is 30 days old or over.

I'll try your code.

Thanks.

Tom

 

[THWatson]

Duane
Using your generic list box also results in an error in the report.

I give up. I am going to abandon the <ALL> idea. I suspect that it will be infrequent when that will be needed anyway.

Thanks for trying.

Tom