Access Multi-select List Box w/ Date Range problem.

A

Adam

AllenBrown or Group:

I've been working through this but cannot get the text items in my
list passed as a string to my query or report. When I select something
from the list, I get a parameter prompt for, "CustomerName", and when
I leave it blank I get, "ODBC error 3146 Call Failed". Although
everything works when I get rid of multi-select in the list. I've
followed examples from several sites including Allenbrown but seem to
be overlooking something. Below is the on click event for my command
button if anyone has any suggestions: Thank you for any assistance you
can provide.

'Form: [New Form]
'List Box: lstCustName
'Date Field: CLOSE_OUT_DATE
'Table: tblCustNames
'Field: CustomerName
'Start Date: txtStartDate
'End Date: txtEndDate
'Report: rptCostMile

rivate Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"


strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If


If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If


'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub


Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
 
D

Douglas J. Steele

If you're getting a prompt for CustomerName, odds are that the actual name
of the field is something else: Customer Name (with a space), CustName, etc.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Adam said:
AllenBrown or Group:

I've been working through this but cannot get the text items in my
list passed as a string to my query or report. When I select something
from the list, I get a parameter prompt for, "CustomerName", and when
I leave it blank I get, "ODBC error 3146 Call Failed". Although
everything works when I get rid of multi-select in the list. I've
followed examples from several sites including Allenbrown but seem to
be overlooking something. Below is the on click event for my command
button if anyone has any suggestions: Thank you for any assistance you
can provide.

'Form: [New Form]
'List Box: lstCustName
'Date Field: CLOSE_OUT_DATE
'Table: tblCustNames
'Field: CustomerName
'Start Date: txtStartDate
'End Date: txtEndDate
'Report: rptCostMile

rivate Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"


strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If


'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If


If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If


'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub


Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
Click to expand...
 
A

Adam

If you're getting a prompt for CustomerName, odds are that the actual name
of the field is something else: Customer Name (with a space), CustName, etc.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




AllenBrown or Group:
Click to expand...
I've been working through this but cannot get the text items in my
list passed as a string to my query or report. When I select something
from the list, I get a parameter prompt for, "CustomerName", and when
I leave it blank I get, "ODBC error 3146 Call Failed". Although
everything works when I get rid of multi-select in the list. I've
followed examples from several sites including Allenbrown but seem to
be overlooking something. Below is the on click event for my command
button if anyone has any suggestions: Thank you for any assistance you
can provide.
Click to expand...
'Form: [New Form]
'List Box: lstCustName
'Date Field: CLOSE_OUT_DATE
'Table: tblCustNames
'Field: CustomerName
'Start Date: txtStartDate
'End Date: txtEndDate
'Report: rptCostMile
Click to expand...
rivate Sub ok_Click()
Click to expand...
'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Click to expand...
strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
Click to expand...
'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If
Click to expand...
If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2
End If
Click to expand...
'Debug.Print strWhere3
DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub
Click to expand...
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you for the suggestion, however I checked the field and it is
spelled correct. I'll continue to scour the internet for a fix as well.
 
D

Douglas J. Steele

If you open the report without the Where clause, do you get the prompt as
well?

If so, then check your report to see whether you've made a mistake referring
to the field there.

If not, exactly what is in strWhere3? Remove the comment from

'Debug.Print strWhere3

and check.
 
A

Adam

If you open the report without the Where clause, do you get the prompt as
well?

If so, then check your report to see whether you've made a mistake referring
to the field there.

If not, exactly what is in strWhere3? Remove the comment from

'Debug.Print strWhere3

and check.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -
Click to expand...

Doug,

I removed the comment 'Debug.Print strWhere3 but I'm unsure of what
else you want me to remove as I'm unfamiliar w/ VB. However, with a
couple of changes now I'm getting a Syntax error in query expression,
'(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007# AND
[CustomerName] IN (CustomerA,CustomerB))'. When I hit debug, it
highlights on: DoCmd.OpenReport strReport, acViewPreview, , strWhere3
 
A

Adam

If you open the report without the Where clause, do you get the prompt as
well?
Click to expand...
If so, then check your report to see whether you've made a mistake referring
to the field there.
Click to expand...
If not, exactly what is in strWhere3? Remove the comment from
Click to expand...
'Debug.Print strWhere3
Click to expand...
and check.
Click to expand...
- Show quoted text -
Click to expand...

Doug,

I removed the comment 'Debug.Print strWhere3 but I'm unsure of what
else you want me to remove as I'm unfamiliar w/ VB. However, with a
couple of changes now I'm getting a Syntax error in query expression,
'(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007# AND
[CustomerName] IN (CustomerA,CustomerB))'. When I hit debug, it
highlights on: DoCmd.OpenReport strReport, acViewPreview, , strWhere3- Hide quoted text -

- Show quoted text -
Click to expand...

Here's the code that throwing that error:

Private Sub ok_Click()

'On Error Goto Err_Handler
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere1 As String 'Where condition for OpenReport.
Dim varItem As Variant 'Selected items
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String
Dim strDelim As String 'Delimiter for this field type.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptCostMile"
strField = "CLOSE_OUT_DATE"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere1 = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere1 = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere1 = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) & _
" And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

'strDelim = """"
'Loop through the ItemsSelected in the list box.
With Me.lstCustName
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & _
.ItemData(varItem) & strDelim & ","
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CustomerName] IN (" & Left$(strWhere2, lngLen) &
")"
End If

If strWhere1 = "" Then
strWhere3 = strWhere2
ElseIf strWhere2 = "" Then
strWhere3 = strWhere1
Else
strWhere3 = strWhere1 & " AND " & strWhere2

End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"ok_Click"
End If
End Sub
 
D

Douglas J. Steele

Adam said:
If you open the report without the Where clause, do you get the prompt as
well?

If so, then check your report to see whether you've made a mistake
referring
to the field there.

If not, exactly what is in strWhere3? Remove the comment from

'Debug.Print strWhere3

and check.
Click to expand...

Doug,

I removed the comment 'Debug.Print strWhere3 but I'm unsure of what
else you want me to remove as I'm unfamiliar w/ VB. However, with a
couple of changes now I'm getting a Syntax error in query expression,
'(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007# AND
[CustomerName] IN (CustomerA,CustomerB))'. When I hit debug, it
highlights on: DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Click to expand...

You'll find what's printed by the Debug.Print statement in the Immediate
window, which you can get to using Ctrl-G.

Is that opening parenthesis actually appearing in the error message? I don't
see anything in your code that should be inserting it.

You should be putting CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007#
in parentheses. As well, since you commented out the line of code that
assigns a value to strDelim, your customer names don't have quotes around
them, like you require.

When corrected, strWhere3 should contain

(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007#)
AND [CustomerName] IN ("CustomerA","CustomerB")
 
A

Adam

If you open the report without the Where clause, do you get the prompt as
well?
If so, then check your report to see whether you've made a mistake
referring
to the field there.
If not, exactly what is in strWhere3? Remove the comment from
'Debug.Print strWhere3
and check.
Click to expand...
Doug,
Click to expand...

I removed the comment 'Debug.Print strWhere3 but I'm unsure of what
else you want me to remove as I'm unfamiliar w/ VB. However, with a
couple of changes now I'm getting a Syntax error in query expression,
'(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007# AND
[CustomerName] IN (CustomerA,CustomerB))'. When I hit debug, it
highlights on: DoCmd.OpenReport strReport, acViewPreview, , strWhere3
Click to expand...

You'll find what's printed by the Debug.Print statement in the Immediate
window, which you can get to using Ctrl-G.

Is that opening parenthesis actually appearing in the error message? I don't
see anything in your code that should be inserting it.

You should be putting CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007#
in parentheses. As well, since you commented out the line of code that
assigns a value to strDelim, your customer names don't have quotes around
them, like you require.

When corrected, strWhere3 should contain

(CLOSE_OUT_DATE Between #01/01/2007# AND #04/01/2007#)
AND [CustomerName] IN ("CustomerA","CustomerB")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- Show quoted text -
Click to expand...

Ahh, you steered me in the right direction when you mentioned the
quotes, and then I remembered that the Customername field is spelled
different depending upon the field which you mentioned yesterday. It
was spelled correctly looking at one table but it's spelled
differently in the table it was referencing so I fixed that. And for
the quotes, I had to remove the single ' in front of strDelim = """"
so that the quotation marks would be placed around the customer names
allowing it to query as text rather than a value. None the less, it's
working now. I appreciate the support Doug!

-Adam
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-select List Box output to Excel File 29
Coding Problem 7
Combox box value used in Query 1
Building WHERE string; Passing Combo Box Value to a Query 1
Crosstab Query Building Report 2
Create Report On the Fly 1
Simplify Code-Delete Query 3
Cleanup Code on Form ?? 3

Top