Filter Report from Text Box Results

[mukraker]

I have a query that returns a list of zip codes in a text box in a form. Now I would like to use the list of zip codes in the text box to filter a report to generate a list of all customers within the corresponding zip codes.

When I run the script I get
""438 - Object doesn't support this property or method"

The text in red is where it is highlighted when I debug.

The Name of the text box is txZips

Some imparted knowledge would be greatly appreciated

Private Sub cmdPreview_Click()

'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.

Dim strReport As String
Dim varItem As Variant
Dim strWhere As String
Dim lngView As Long


strReport = "rptCustomers"
lngView = acViewPreview
varItem = txtZips
strWhere = "1=1"
'
For Each varItem In Me!txtZips.ItemsSelected
strWhere = strWhere & "[ZIP_CODE] Like " & Chr(34) & Me!txZips.Column(0, varItem) & Chr(34) & " or "

Next varItem
On Error Resume Next
strWhere = Left(strWhere, Len(strWhere) - 4)

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere


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

 

[MarkK]

Hi there. 'ItemsSelected' is not a property of a TextBox, it is a property of a ListBox. The code you posted was intended to work with a ListBox and would need to be modified quite considerably to parse a list from a textbox.
Cheers,

 

[pbaldy]

ItemsSelected is a property of a listbox, not a textbox. If that's really a textbox, you're not using a method that will work. What is it and what does it contain exactly?

 

[mukraker]

I kind of wondered if I was attacking this completely wrong. It is indeed a text box. I have a form that someone else made that returns a list of zip codes within the mileage radius that I enter.

I created a report from all of our customers and I am looking for some way to auto select all the zip codes listed in the results that are displayed in the text box and use that to filter the report.

The idea is that if I type in a zip code and radius I can generate a report that shows how many customers fall within the radius.


It would be useful for building new truck routes.

 

[pbaldy]

It depends on exactly what is in the textbox (which is why I asked). If it's formatted appropriately, this type of thing would work:

strWhere = "[ZIP_CODE] IN(" & Me.txZips & ")"

 

[mukraker]

Thanks for the quick reply Paul.

I added the line

strWhere = "[ZIP_CODE] IN(" & Me.txZips & ")"

and commented out the other two as you can see below. Not when I run the code nothing happens. When I run the script however if the rptCustomers is already open it will close it. So I seem to think it reaches that point but I do not know why it does not open the report. I am sure I am doing something stupid.



Private Sub cmdPreview_Click()

'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.

Dim strReport As String
Dim varItem As Variant
Dim strWhere As String
Dim lngView As Long


strReport = "rptCustomers"
lngView = acViewPreview
'varItem = txtZips
strWhere = "1=1"


'For Each varItem In Me!txtZips.ItemsSelected
strWhere = "[ZIP_CODE] IN(" & Me.txtZips & ")"
'strWhere = strWhere & "[ZIP_CODE] Like " & Chr(34) & Me!txZips.Column(0, varItem) & Chr(34) & " or "

'Next varItem
On Error Resume Next
strWhere = Left(strWhere, Len(strWhere) - 4)

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere


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

 

[pbaldy]

Get rid of these two lines and try it:

On Error Resume Next
strWhere = Left(strWhere, Len(strWhere) - 4)

Also, what is the result of the Debug.Print (since you still haven't said what the textbox contains)?

 

[mukraker]

Sorry Paul.

the Text Box txtZips has a list of zip codes for instance when I type in 37115 and 10 miles the text box returns:

37072,37075,37076,37080,37115,37122,37138,37152,37189,37201,37203,37204,37206,37207,37208,37209,37210,37212,37213,37214,37216,37217,37218,37219,37228

Or if I type in 11375 and 3 miles I get:
10034,11208,11354,11355,11358,11365,11366,11367,11368,11369,11370,11372,11373,11374,11375,11377,11378,11379,11385,11415,11416,11417,11418,11419,11420,11421,11432,11433,11435

After this is returned I click on the button to run the code to filter the rptCustomers with just the zip codes listed int he box.

the Debug.Print result is:

Runtime error 3464 Data Mismatch in criteria expression.

 

[MarkK]

Did you get rid of these two lines ...

On Error Resume Next
strWhere = Left(strWhere, Len(strWhere) - 4)

... as per pbaldy's post #7?

Do you still get ...

Runtime error 3464 Data Mismatch in criteria expression.

... after those lines are removed?

 

[pbaldy]

If the data type of the zip code field is text, as I suspect it is, the contents of the text box is not appropriate. For a text field it would have to be:

'37072','37075','37076',...

Presuming that's the case, can you modify the process that fills the textbox to add the single quotes around each value?

 

[MarkK]

Or perhaps it would work to to Join(Split()) ...

debug.print "'" & Join(Split(Me.txtZip, ", "), "', '") & "'"

 

[pbaldy]

I've used Split() many times, but wasn't aware of Join(). That looks like it could work.

 

[mukraker]

lagbolt and Paul,

you guys are amazing!! I added the Join(Split()) ... Then instead of adding a quote to each zip I went back to the original table and changed the zipcode from a text field to a number. Now it works exactly as I want it to.

Thank you very much. Thanks to you guys I just might get some sleep tonight.

 

[pbaldy]

A cautionary note. Zip codes are normally stored as text because of the leading zero that some have. A numeric field will not hold the leading zero.

 

[mukraker]

Thanks Paul. I didn't consider that. I will try to see if I can add quotes to the zip codes later. In the meantime I can at least use what I have so far.