Postal Codes 10001-10005 as a parameter

Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Postal Code.

Can any one help me regarding the postal code. As we have seen in the
website we put postal code and it will display the nearest distance... can
any one help me out regarding this ... like what would be the formula to
calculate the distance...
I would highly appreciate.
Thanks
Rogers wrote:
> Can any one help me regarding the postal code. As we have seen in the
> website we put postal code and it will display the nearest
> distance... can any one help me out regarding this ... like what
> would be the formula to calculate the distance...
> I would highly appreciate.
> Thanks
I think you'll need latitude and longitude to do the calculation. See
this page for an example:
http://px.sklar.com/code.html/id=1062
You can buy the database here:
http://www.zipcodeworld.com/
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yeah but they do not have the long lats.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHyMDbKuFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Rogers wrote:
> I think you'll need latitude and longitude to do the calculation. See this
> page for an example:
> http://px.sklar.com/code.html/id=1062
> You can buy the database here:
> http://www.zipcodeworld.com/
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Postal code search and LIKE statement

I'm trying to create a form that allows someone to find anaddress from a postal code search in the database. My query works exactly as I’dlike in query builder using the following select:

Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@.POSTCODE+’%’

I then pass the user entered post code to the select statementwhich is executed. However, I’m getting some odd behaviour. Assuming there isan address in the database with the post code “LS11 0ES”...

If I search for LS11, nothing is returned;

If I search for LS11%, nothing is returned;

If I search for %LS11%, nothing is returned;

If I search for %LS11%%, the data is returned.

If I search for %LS%1%%, the data is returned (as is LS210ES etc. etc.).

However, I want the user to be able to enter shorter searchstrings and it to pull all the data back out, so they can enter a substringsuch as LS and it will pull out all the data without the users needing to enterthe full pattern of % symbols.

If I go into query builder (in visual web developer) andenter just “LS” it works as I’d want, but not when pulled from a web page.

Any ideas?

Thanks

Look for other causes. I'm guessing somewhere in your code, you are removing the last % in postcode either before assigning it to the parameter, or modifying it during the selecting event.|||Thanks.At the moment I have a details view bound to an objectsource which in turn is bound to the above query and the parameter taken in from the text box’s .text property which the user types in.How can I trace where its failing?Thanks|||

A) response.write all your variables

or

B) Use a debugger like visual studio

sql