Use Replace function to reduce postcode to postcode region

I've never used ASP, but at a guess it's a question of using ANSI SQL
wildcard characters rather than the Access norm of Jet SQL. From Help:

Matching character Microsoft Jet SQL ANSI SQL
Any single character ? _ (underscore)
Zero or more characters * %

So try changing
Like ""?#*""
to
Like ""_#%""


On 26 Jan 2007 13:01:27 -0800, "Gelatinous_Blob"
wrote:

John, you were right, an input mask had got in there somehow.

Removing it had the desired effect of reducing all the postcodes to one
or two characters.

But when I apply this to an ASP page, for some reason my recordset only
returns matches against two-character postcode areas.

So if my query string value is EH, that's fine, everything in the
Edinburgh area is returned, but if it's G, the recordset comes back
empty.

This just seems plain weird to me, but no doubt I've missed something
obvious. Any ideas? Here's the code for the recordset.

%
Dim RSretailers
Dim RSretailers_numRows

Set RSretailers = Server.CreateObject("ADODB.Recordset")
RSretailers.ActiveConnection = MM_cid_conn_STRING
RSretailers.Source = "SELECT BusinessCity, BusinessCountry,
BusinessPhone, BusinessPostalCode, BusinessState, BusinessStreet,
BusinessCompany, BusinessID, BusinessType, BusinessWebPage FROM
Retailers WHERE IIf([BusinessPostalCode] Like
""?#*"",Left([BusinessPostalCode],1),Left([BusinessPostalCode],2)) = '"
+ Request.QueryString("postcode") + "' ORDER BY BusinessCity ASC,
Company ASC"
RSretailers.CursorType = 0
RSretailers.CursorLocation = 2
RSretailers.LockType = 1
RSretailers.Open()

RSretailers_numRows = 0
%


On 18 Jan, 20:12, John Nurick wrote:
I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!

On Thu, 18 Jan 2007 11:56:53 -0600, Marshall Barton

wrote:
That code can not add extraneous characters.

Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Got my hopes up there, but no joy. Tried all three wild cards but this
doesn't seem to do the trick.

One curious thing:

Records successfully filter on single character numerical values, but
not single character text values. It does however work on double
character text values.

I think I'll take this on to an ASP group and see what I come up with.
I'll post any resolution here for anyone interested.

Thanks for your help so far.



On 26 Jan, 22:32, John Nurick wrote:
I've never used ASP, but at a guess it's a question of using ANSI SQL
wildcard characters rather than the Access norm of Jet SQL. From Help:

Matching character Microsoft Jet SQL ANSI SQL
Any single character ? _ (underscore)
Zero or more characters * %

So try changing
Like ""?#*""
to
Like ""_#%""

On 26 Jan 2007 13:01:27 -0800, "Gelatinous_Blob"





wrote:
John, you were right, an input mask had got in there somehow.

Removing it had the desired effect of reducing all the postcodes to one
or two characters.

But when I apply this to an ASP page, for some reason my recordset only
returns matches against two-character postcode areas.

So if my query string value is EH, that's fine, everything in the
Edinburgh area is returned, but if it's G, the recordset comes back
empty.

This just seems plain weird to me, but no doubt I've missed something
obvious. Any ideas? Here's the code for the recordset.

%
Dim RSretailers
Dim RSretailers_numRows

Set RSretailers = Server.CreateObject("ADODB.Recordset")
RSretailers.ActiveConnection = MM_cid_conn_STRING
RSretailers.Source = "SELECT BusinessCity, BusinessCountry,
BusinessPhone, BusinessPostalCode, BusinessState, BusinessStreet,
BusinessCompany, BusinessID, BusinessType, BusinessWebPage FROM
Retailers WHERE IIf([BusinessPostalCode] Like
""?#*"",Left([BusinessPostalCode],1),Left([BusinessPostalCode],2)) = '"
+ Request.QueryString("postcode") + "' ORDER BY BusinessCity ASC,
Company ASC"
RSretailers.CursorType = 0
RSretailers.CursorLocation = 2
RSretailers.LockType = 1
RSretailers.Open()

RSretailers_numRows = 0
%

On 18 Jan, 20:12, John Nurick wrote:
I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!

On Thu, 18 Jan 2007 11:56:53 -0600, Marshall Barton

wrote:
That code can not add extraneous characters.

Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.- Hide quoted text -- Show quoted text -