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

 

[danvlas]

Where PostCode Like "SE1*"

or

Where Left(PostCode,3)="SE1"

or

Where Instr(PostCode,"SE1")=1

HTH


Daniel Vlas
Systems Consultant

 

[Remou]

You can use like.

... WHERE PostCode Like "SE1*

 

[Drisconsult]

Hello All

As usual, people have gone out of their to help an old fart. Can't thank you all enough. I will of course get all the thanks for finding the solution.

Regards
Terence

 

[JoeAtWork]

My understanding is you don't want results like SE15 if search is for SE1.

In which case I think what you want would be more like:

WHERE TRIM(PostCode) = "SE1"

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Golom]

I'm not too clear on this. You state

... a method for searching on three digits only, ignoring all other digits that might follow

but I also get the impression that "SE15", "SE16", etc. should not be included.

If we are ... ignoring all other digits ... then why should those strings not be included?

 

[Drisconsult]

Hello Golom

The secretary who uses the program searches on all postcodes, not just SE1. Her problem was that when she entered SE1, the result was another hundred schools whose postcodes were:

SE13, SE15, SE16, SE19 etc.

SE1 is just one of the postcodes that she lists every day in order to place a student in a school. Then she can see which schools are offering a teaching subject to match the student's home area.

Regards
Terence
London

 

[Golom]

OK so you want a search that finds only SE1 but doesn't find SE1 followed by anything else. Is that right?

If so

Where PostCode = [Enter Postal Code]

Should do it.

Alternatively you could do a little training and use

Where PostCode LIKE [Enter Postal Code]

then teach them to enter "SE1" for just that Postal Code or enter "SE1[red]*[/red]" to get all Postal Codes starting with SE1.

If that's beyond their skill set, then give them a set of radio buttons
[tt]
- Exact Match SE1
- Starts With SE1*
- Ends With *SE1
- Contains *SE1*
[/tt]

 

[Drisconsult]

Hello Golom

The Secretary that uses the program will be searching for a number of schools on an hourly basis. When a student has to be placed in a school, she will know the postcode of the student. She then looks up all schools with that postcode to see if they are offering a place in the student's subject, such as mathematics.

There are 480 schools entered at the moment. This means that there are huge numbers of different postcodes. What she is complaining about is when she enters a school with just three characters such as SE1, she gets all SE1 schools plus SE12, SE13, SE16, SE19 and so on.

The search method is using a parameter query, which the secretaries seem to like, as the previous program they used had search methods based on this procedure.

To sum up, she needs to be able to isolate SE1 from the other postcodes that also begin with SE1.

Hope this helps.
Regards
Terence

 

[Golom]

As I said

Where PostCode = [Enter Postal Code]

will find only SE1 postal codes if they type SE1 in response to the parameter prompt.

If they want the flexibility to get SE1 only, but also be able to get the "SE1+anything else" codes, then go with the LIKE version and teach them about wildcards.

For example "SE1" returns only SE1; "SE1*" returns everything that starts with SE1; etc.

 

[PHV]

Like [your parameter here] & '[!0-9]'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886