Searcing on three digits only

[drisconsult]

Hello All

A couple of years ago I completed a program for a college to place trainee teachers in various schools on teaching practice. Most students were allocated schools within their London post codes. I have just received a request from the college, asking me if I can modify the search facility on post codes.

When the user searches for schools with a SE1 postcode, the result produces all school with SE1, such as SE15, SE16 and so on. Is there a method for searching on three digits only, ignoring all other digits that might follow?

Regards
Terence
London
1 August 2008

 

[namliam]

Postcode like 'SE1*'

 

[sbenj69]

Hello All

A couple of years ago I completed a program for a college to place trainee teachers in various schools on teaching practice. Most students were allocated schools within their London post codes. I have just received a request from the college, asking me if I can modify the search facility on post codes.

When the user searches for schools with a SE1 postcode, the result produces all school with SE1, such as SE15, SE16 and so on. Is there a method for searching on three digits only, ignoring all other digits that might follow?

Regards
Terence
London
1 August 2008

Like Left([yoursearchfield],3) & "*"

 

[raskew]

Hi -

Did a Google on London postcodes. Found two types of examples.

1) Where a space follows the 3 or 4 digit post code, e.g. SE1 7BJ

2) Where no space follows the 3 or 4 digit post code, e.g. SE17BJ

If example 1 is correct, then:

Like "SE1 *" will give you what you're looking for (note the space)

If example 2 is correct, and you're trying to return all SE1 post codes, but excluding SE15, think there's a problem.
Neither Like "SE1*" nor Like Left([yoursearchfield],3) & "*" is
going to return just SE1, and exclude SE15.

Bob

 

[namliam]

Then again if you want NUMBER/Didget specificaly.... You can use # instead of *
* Gives anything after the third character
# requires exactly one didget

I.e.
1234 like "123#" gives true
1234 like "123*" gives true
12345 like "123#" gives false
12345 like "123*" gives true

 

[Brianwarnock]

Why do we need to use Like?
I would create a field left(postcode,4) and search for ="SE1 "

Brian

 

[KenHigg]

Instead of 'Like' could you use 'FondOf'?

Or to return the inverse could you use 'Dislike' or maybe 'Hate'.

(Since the original poster hasn't checked in for 6 hours I thought no harm done in going a little off topic... )

 

[Brianwarnock]

(Since the original poster hasn't checked in for 6 hours I thought no harm done in going a little off topic... )

During the years I've been posting i am still amazed at the number of people who don't come back, or take their time over it.

Brian

 

[sbenj69]

Hmmmm, apparently I misunderstood what he was wanting. I thought he was wanting all SE1's and >SE1's. What kind of messed up postal scheme is this?

j/k guys

So, is like "SE1" a stand alone postal code? I figured it was a prefix..... sort of like all of Illinois zip codes are between 60000 and 62999, so 60, 61, and 62 for the first 2 numbers means it is in Illinois.

 

[Brianwarnock]

Don't apologise it is daft, a post code consists of 2 parts separated by a space. The second part is numeric alpha alpha ie 6BW.
The first part is the problem
It can be Like
L1
L18
WA22
SE1

I once had a discussion with 1 of the designers and told him that to design something like that in the computer age was just bonkers, he couldn't see it.

Brian

 

[drisconsult]

Hello All
Forgive me for not making my request clearer. There are 420 schools and colleges on the database. All these schools and colleges have a post code prefix. Here are some of the codes:

EC2Y
SE1
NW12
SE19
W12
SW1W
SE16
NW1
NW12
SE14
And many more

At the moment there are 14 schools and colleges with a SE1 prefix. There are 9 schools and colleges with a SE19 prefix.

When the secretary enters SE1 in the parameter query dialog box, she sees all schools and colleges that begin with SE1 (23 schools). She finds this very frustrating. How can I filter out the other post codes prefixes in a parameter query. The parameter query is used because this is what I inherited, and the secretaries who run this system are happy with this method of searching.

Regards
Terence
London
How can

 

[georgedwilkinson]

So you're saying that you only want to see results that are "exactly SE1", no stinking "SE11", no stinking "SE12", no stinking "SE1Anything"...is that correct?

I think Bob (raskew) answered this in post #4 (option 1).

Is there a reason that didn't work for you?

 

[Brianwarnock]

Its true Bob gave a solution but I still think that creating a search field as per my post#6 is the simplest for the user.

Brian

 

[raskew]

If you have Northwind available, here's an example that might make it a little clearer. Copy the following to a new query:

SELECT Products.QuantityPerUnit
FROM Products
WHERE (((Products.QuantityPerUnit) Like [enter quantity] & "*"));

The QuantityPerUnit field has some records that begin with 10, others that begin with 100.

If you run the query and are prompted to [enter quantity], if you enter 10 & hit OK, you'll get all the records that begin with both 10 and 100. If, however, you enter 10 followed by a space, it'll return only those quantities with 10, and not 100.

So, in your situation, enter "SE1 " rather than "SE1" (quotes for illustrative purpose only).

HTH - Bob

Added:

Brian - Either way accomplishes the same thing. It's getting that space in there (as per both of our examples) that's important.

 

[drisconsult]

Hello Raskew
Yes your:

Where a space follows the 3 or 4 digit post code, e.g. SE1 7BJ

Does work.

Forget to mention that this program is compiled. Secretaries do not have access to Queries or any other object. When I have completed this update I will pass it on to a colleague who has the compiler, and he will do the rest. Never been able to get hold of such a jewel myself.

Thank you all for your help in this matter. I do have another problem that I have never been able to solve and cannot find a solution to anywhere. But that's another time.

As to being away for six hours, don't you people have a life? Try orientering, it gives a whole new meaning to being alive. But is does mean doing about 15 miles every other day.

Regards
Terence
London

 

[raskew]

Hi -

Try orienteering, it gives a whole new meaning to being alive.

Thanks but no thanks. Twenty-seven years in the US Army provided all the 'whole new meaning to being alive' that I will ever need.

Best wishes - Bob

P.S. Post your other problem.

 

[Brianwarnock]

As to being away for six hours, don't you people have a life? Try orientering, it gives a whole new meaning to being alive. But is does mean doing about 15 miles every other day.

Regards
Terence
London

Of course we have a live, walked 14 miles in the Vale of Llangollen last Sunday, but wouldn't ask a question just before leaving.

Brian