|
 |
access_asp thread: trimming text for a postcode search
Message #1 by jake williamson 28 <jake.williamson@2...> on Tue, 06 Aug 2002 09:52:55 +0100
|
|
hello!
using dreamweaver mx to build uk postcode search page. at the moment i've
got this sql:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
where 'varPostcode' comes from a txt field in a form on the previous page.
got a problem though:
how do i trim my 'varPostcode' text sting? for example, my customer enters
his/hers postcode of:
BS10 5PL
and my dealer has the postcode of:
BS10 5KL
i want to trim the string to have just the first 2 or 3 letters from the
customers postcode. that way the query is looking for dealers in an area
rather than on the same road as the customer!!
someone has suggested the following using the LTRIM function:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE LTRIM ('varPostcode',4)
ORDER BY DEALER ASC
but it dont work - can see the theory but it's not happening in practace!
any ideas out there oh gurus??
cheers,
jake
Message #2 by "Al Vazquez" <avazqu1@p...> on Tue, 6 Aug 2002 11:57:44
|
|
Hi Jake,
I know this is an ASP forum, but I think many people would benefit from
extensive use of javascript as it reduces the amount of commands executed
by the server (JS runs in the browser, ASP runs on the server). I would
recommend that instead of using a standard submit button, you use a regular
button with the attribute onClick="submitForm()". In the head tags of your
page, start a script, and write a function called submitForm() in this manner:
<head>
<script language="javascript">
function submitForm()
{
postCode = document.FORMNAME.FIELDNAME.value
postCode = postcode.substr(0,2)
document.FORMNAME.FIELDNAME.value = postCode
document.FORMNAME.submit()
}
</script>
</head>
There are many other things you can do with browser scripting in both
JavaScript and VBscript, especially data validation to make sure the user
filled out all the fields BEFORE you submit to the database. Hope this helps.
Cheers,
Al
> hello!
using dreamweaver mx to build uk postcode search page. at the moment i've
got this sql:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
where 'varPostcode' comes from a txt field in a form on the previous page.
got a problem though:
how do i trim my 'varPostcode' text sting? for example, my customer enters
his/hers postcode of:
BS10 5PL
and my dealer has the postcode of:
BS10 5KL
i want to trim the string to have just the first 2 or 3 letters from the
customers postcode. that way the query is looking for dealers in an area
rather than on the same road as the customer!!
someone has suggested the following using the LTRIM function:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE LTRIM ('varPostcode',4)
ORDER BY DEALER ASC
but it dont work - can see the theory but it's not happening in practace!
any ideas out there oh gurus??
cheers,
jake
Message #3 by "Darrell" <darrell@b...> on Tue, 6 Aug 2002 13:09:10 +0100
|
|
Hi Jake
You can simply change your SQL to the following:
SELECT LEFT(POSTCODE,3), OTHER_COLUMNS
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
Cheers
Darrell
-----Original Message-----
From: jake williamson 28 [mailto:jake.williamson@2...]
Sent: 06 August 2002 09:53
To: Access ASP
Subject: [access_asp] trimming text for a postcode search
hello!
using dreamweaver mx to build uk postcode search page. at the moment i've
got this sql:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
where 'varPostcode' comes from a txt field in a form on the previous page.
got a problem though:
how do i trim my 'varPostcode' text sting? for example, my customer enters
his/hers postcode of:
BS10 5PL
and my dealer has the postcode of:
BS10 5KL
i want to trim the string to have just the first 2 or 3 letters from the
customers postcode. that way the query is looking for dealers in an area
rather than on the same road as the customer!!
someone has suggested the following using the LTRIM function:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE LTRIM ('varPostcode',4)
ORDER BY DEALER ASC
but it dont work - can see the theory but it's not happening in practace!
any ideas out there oh gurus??
cheers,
jake
Message #4 by "Al Vazquez" <avazqu1@p...> on Tue, 6 Aug 2002 15:19:02
|
|
Will this look at only the first 3 characters of the string the user
inputted? It looks to me like it will only select the first 3 characters
of the field in the database.
Cheers,
Al
> Hi Jake
You can simply change your SQL to the following:
SELECT LEFT(POSTCODE,3), OTHER_COLUMNS
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
Cheers
Darrell
-----Original Message-----
From: jake williamson 28 [mailto:jake.williamson@2...]
Sent: 06 August 2002 09:53
To: Access ASP
Subject: [access_asp] trimming text for a postcode search
hello!
using dreamweaver mx to build uk postcode search page. at the moment i've
got this sql:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
where 'varPostcode' comes from a txt field in a form on the previous page.
got a problem though:
how do i trim my 'varPostcode' text sting? for example, my customer enters
his/hers postcode of:
BS10 5PL
and my dealer has the postcode of:
BS10 5KL
i want to trim the string to have just the first 2 or 3 letters from the
customers postcode. that way the query is looking for dealers in an area
rather than on the same road as the customer!!
someone has suggested the following using the LTRIM function:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE LTRIM ('varPostcode',4)
ORDER BY DEALER ASC
but it dont work - can see the theory but it's not happening in practace!
any ideas out there oh gurus??
cheers,
jake
Message #5 by jake williamson 28 <jake.williamson@2...> on Tue, 06 Aug 2002 15:59:29 +0100
|
|
hi,
yep, i'd just build the sql and got just that. i need tell it look at the
first 3 letters of the entered postcode and match them to the first 3
letters of the dealers postcode.
so i tried:
SELECT POSTCODE, OTHER_COLUMNS
FROM DEALERS
WHERE LEFT(POSTCODE,3) LIKE LEFT('varPostcode',3)
ORDER BY DEALER ASC
and this does the trick!
thank you to everyone who sent solutions, works a treat - be lost without
ya!
jake
on 6/8/02 3:19 pm, Al Vazquez at avazqu1@p... wrote:
> Will this look at only the first 3 characters of the string the user
> inputted? It looks to me like it will only select the first 3 characters
> of the field in the database.
>
> Cheers,
> Al
>
>> Hi Jake
>
> You can simply change your SQL to the following:
>
> SELECT LEFT(POSTCODE,3), OTHER_COLUMNS
> FROM DEALERS
> WHERE POSTCODE LIKE 'varPostcode'
> ORDER BY DEALER ASC
>
> Cheers
> Darrell
Message #6 by "Darrell" <darrell@b...> on Tue, 6 Aug 2002 16:01:30 +0100
|
|
G'Day Al
Nope that's me just not paying attention to the question :o)
Cheers
Darrell
-----Original Message-----
From: Al Vazquez [mailto:avazqu1@p...]
Sent: 06 August 2002 15:19
To: Access ASP
Subject: [access_asp] RE: trimming text for a postcode search
Will this look at only the first 3 characters of the string the user
inputted? It looks to me like it will only select the first 3 characters
of the field in the database.
Cheers,
Al
Message #7 by "Rob Parkhouse" <rparkhouse@o...> on Wed, 7 Aug 2002 03:09:32
|
|
> hello!
using dreamweaver mx to build uk postcode search page. at the moment i've
got this sql:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE 'varPostcode'
ORDER BY DEALER ASC
where 'varPostcode' comes from a txt field in a form on the previous page.
Hi Jake,
the final solution (where you truncate both the database field and the
search criteria) posted by you of course works. The point you seemed to
have missed at the start is that you can use wild characters when using
LIKE. For example:
SELECT *
FROM DEALERS
WHERE POSTCODE LIKE "B10*"
ORDER BY DEALER ASC
which says give any record where the postcode has the 1st 3 chars = B10
and I don't care what follows.
Regards
|
|
|
View Cart
