using dates in a multiple select list box

[Guest]

I have used Allen Browne's code for selecting multiple items off a list box
and printing them in a report (Thanks so much Allen!) for usually text and
number fields. However, this time I am using a list box that contains only
one field, a date field and it doesn't seem to be working. I do not get any
kind of error message, but the report is blank. I think it has to do with
the field being a date. Please help! Here are the specifics. The code is
this:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptPrint List by Bay"

'Loop through the ItemsSelected in the list box.
With Me.DrDate
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[InvDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "DrDate: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

I have my list box set to Simple.

Thanks for any help you can give me.

 

[Douglas J Steele]

Dates need to be delimited with #, not ", and they need to be in mm/dd/yyyy
format, regardless of what your regional settings may be. (Okay, this isn't
strictly true: you can use any unambiguous format, such as yyyy-mm-dd or dd
mmm yyyy. The point is, it won't work reliably if your Short Date format has
been set to dd/mm/yyyy)

To meet both of these requirements at once, change the line of code

strDescrip = strDescrip & """" & .Column(0, varItem) & """, "

to

strDescrip = strDescrip & Format(.Column(0, varItem), "\#mm\/dd\/yyyy\#") &
", "

 

[Guest]

Thanks so much for your suggestion, I did what you told me to, however the
report is still blank.

Any other thoughts??

Thanks so much!

Dates need to be delimited with #, not ", and they need to be in mm/dd/yyyy
format, regardless of what your regional settings may be. (Okay, this isn't
strictly true: you can use any unambiguous format, such as yyyy-mm-dd or dd
mmm yyyy. The point is, it won't work reliably if your Short Date format has
been set to dd/mm/yyyy)

To meet both of these requirements at once, change the line of code

strDescrip = strDescrip & """" & .Column(0, varItem) & """, "

to

strDescrip = strDescrip & Format(.Column(0, varItem), "\#mm\/dd\/yyyy\#") &
", "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have used Allen Browne's code for selecting multiple items off a list box
and printing them in a report (Thanks so much Allen!) for usually text and
number fields. However, this time I am using a list box that contains only
one field, a date field and it doesn't seem to be working. I do not get any
kind of error message, but the report is blank. I think it has to do with
the field being a date. Please help! Here are the specifics. The code is
this:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptPrint List by Bay"

'Loop through the ItemsSelected in the list box.
With Me.DrDate
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[InvDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "DrDate: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

I have my list box set to Simple.

Thanks for any help you can give me.

 

[Douglas J. Steele]

My fault. I didn't look that closely at Allen's code, and missed the fact
that he was building two separate strings.

You also need to change

strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","

to

strWhere = strWhere & Format(.ItemData(varItem), "\#mm\/dd\/yyyy\#") &
","


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks so much for your suggestion, I did what you told me to, however the
report is still blank.

Any other thoughts??

Thanks so much!

Dates need to be delimited with #, not ", and they need to be in
mm/dd/yyyy
format, regardless of what your regional settings may be. (Okay, this
isn't
strictly true: you can use any unambiguous format, such as yyyy-mm-dd or
dd
mmm yyyy. The point is, it won't work reliably if your Short Date format
has
been set to dd/mm/yyyy)

To meet both of these requirements at once, change the line of code

strDescrip = strDescrip & """" & .Column(0, varItem) & """, "

to

strDescrip = strDescrip & Format(.Column(0, varItem), "\#mm\/dd\/yyyy\#")
&
", "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have used Allen Browne's code for selecting multiple items off a list box
and printing them in a report (Thanks so much Allen!) for usually text
and
number fields. However, this time I am using a list box that contains only
one field, a date field and it doesn't seem to be working. I do not
get any
kind of error message, but the report is blank. I think it has to do
with
the field being a date. Please help! Here are the specifics. The
code is
this:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See
note 1.
strDoc = "rptPrint List by Bay"

'Loop through the ItemsSelected in the list box.
With Me.DrDate
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[InvDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "DrDate: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

I have my list box set to Simple.

Thanks for any help you can give me.