How to pull county "New york", if the user type Manhattan in the access database search field.

Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!

one way is to use a listbox so they must choose from the list of correct
available counties - so they couldn't enter New York if it is not valid

another way is to use vb code with an if/then

If textboxXYA="New York" then
texboxXYA = "Manhattan"
End If

but the vb approach requires you to anticipate what the user will enter -
and that is a notoriously difficult task....
--
NTC


" wrote:

Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!

wrote:
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan


You didn't say where these values are entered nor how the
search is accomplished.

If it's typed into a form text box, then you could add a
check to the code that constructs the SQL statement.

If the report's query prompts for the county, then use a
criteria like:
IIf([Enter County] = "New York", "Manhatten", [Enter
County])

--
Marsh
MVP [MS Access]

On Mon, 05 Nov 2007 12:59:05 -0800, wrote:

Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!

How many counties in the state? How many state residents know all the
counties? I've never lived in NY state, but I thought Mahattan was a borough,
not a county. Is there a county with the same name? That is not completely
unheard of.

Chuck
--

On Nov 5, 6:36 pm, NetworkTrade
wrote:
one way is to use a listbox so they must choose from the list of correct
available counties - so they couldn't enter New York if it is not valid

another way is to use vb code with an if/then

If textboxXYA="New York" then
texboxXYA = "Manhattan"
End If

but the vb approach requires you to anticipate what the user will enter -
and that is a notoriously difficult task....
--
NTC



" wrote:
Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!- Hide quoted text -

- Show quoted text -

I have a query setup that asks the user to enter "What Specialty are
you looking for?", after the user enters the speicalty, the query than
will ask "In what county?".

On Nov 6, 7:34 am, Chuck wrote:
On Mon, 05 Nov 2007 12:59:05 -0800, wrote:
Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!

How many counties in the state? How many state residents know all the
counties? I've never lived in NY state, but I thought Mahattan was a borough,
not a county. Is there a county with the same name? That is not completely
unheard of.

Chuck
--- Hide quoted text -

- Show quoted text -

Some people might call it a New York County, or Manhattan county, Just
like some might call Brooklyn a county instead of calling it a Kings
County. We just want to make sure that we covered all the choices.

wrote:
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan


Please keep the correspondence in the newsgroups.

If you will have aliases for more than one or two counties,
then I think you should have a table for them. This table
only needs two fields, one (the PK) for the alternate name
and one for the official name. Then you can Join this table
to your providers table in a query so you can display the
official name.

Or, you could use a DLookup on the county alias table in the
count text box's AfterUpdate event to store the official
name.

--
Marsh
MVP [MS Access]

On Mon, 05 Nov 2007 12:59:05 -0800, wrote:

Hello,
I have a database lets the user search for provider by specialty and
county. So, if i want to serach orthopedist in Manhattan, I would
type in Orthopedist in the specialty and Manhattan as the county.
What I would like to do is, if the user enters either New York or
Manhattan in the county field, they will get Manhattan as the county
in both cases. My table has Manhattan as the county, not New York.
Is it possible to do that?

Table
Provider Name: Specialty County
John Orthopedist Manhattan

The user Types in "Orhthopedist" as the specialty and "New York" as
the county, I want the database to give me

John Orthopedist Manhattan

thanks!

Is education out of the question?

Instead of a text box, use a combo box.
Then if the user tries to type an entry, you can have a message box open
stating,
"No such county - Pick from the list"

Chuck
--