Syntax assistance on excluding certain fiedls

[Guest]

In field [street], I have inserted the following:

Not Like "* Unit *" And Not Like "* apt. *" And Not Like "* apt *" And Not
Like "* apartment *" And Not Like "* # *"

However, when field [city] equals New York, I wish to include the excluded
streets. The reason being no one New York (manhattan) lives in a house,
everyone has an apartment number.

What is the syntax within a query. I use access 2000. I don't know sql,
basic or other programming language so help on a simplistic basis would be
appreciated. Thanks in advance to any readers who might respond.

 

[John Spencer]

Is this the only criteria you have applied in the query?

If so and if you are using the grid, just go to the next line in the criteria
section and enter "New York" under the City field.

Field: City
Criteria (Line 1): <Leave this blank>
Criteria (Line 2): "New York"

If you have other criteria on the first line (besides the Street criteria), you
will need to repeat the criteria in criteria line 2.

If you were entering this directly into the SQL text window, you would have
something like

WHERE City = "New York"
OR (Street Not Like "* Unit *"
And Street Not Like "* apt. *"
And Not Street Like "* apt *"
And Street Not Like "* apartment *"
And Street Not Like "* [#] *")

By the way # is a wild card character meaning any number character. So Street
Not Like "* # *" translates to any address with a single number surrounded by
spaces. So "No. 1 South Main" would be excluded under this criterion.

I think what you want is
Street Not Like "* [#] *"

That will exclude street addresses with a # character surrounded by spaces.

 

[Guest]

There are numerous cities since the local practice for the post office is to
refer to the neighborhood in the City section, i.e. Jamaica, New York, but
Manhattan is only referred to as New York, New York. I appreciate Mr.
Spencer's help, but the assumption of only one city is not applicable.

If any reader has a suggestion to exclude all apartments except in
Manhattan, , same would be appreciated.

Is this the only criteria you have applied in the query?

If so and if you are using the grid, just go to the next line in the criteria
section and enter "New York" under the City field.

Field: City
Criteria (Line 1): <Leave this blank>
Criteria (Line 2): "New York"

If you have other criteria on the first line (besides the Street criteria), you
will need to repeat the criteria in criteria line 2.

If you were entering this directly into the SQL text window, you would have
something like

WHERE City = "New York"
OR (Street Not Like "* Unit *"
And Street Not Like "* apt. *"
And Not Street Like "* apt *"
And Street Not Like "* apartment *"
And Street Not Like "* [#] *")

By the way # is a wild card character meaning any number character. So Street
Not Like "* # *" translates to any address with a single number surrounded by
spaces. So "No. 1 South Main" would be excluded under this criterion.

I think what you want is
Street Not Like "* [#] *"

That will exclude street addresses with a # character surrounded by spaces.

In field [street], I have inserted the following:

Not Like "* Unit *" And Not Like "* apt. *" And Not Like "* apt *" And Not
Like "* apartment *" And Not Like "* # *"

However, when field [city] equals New York, I wish to include the excluded
streets. The reason being no one New York (manhattan) lives in a house,
everyone has an apartment number.

What is the syntax within a query. I use access 2000. I don't know sql,
basic or other programming language so help on a simplistic basis would be
appreciated. Thanks in advance to any readers who might respond.

 

[Randy Harris]

You're not making your requirements clear. You originally asked for "when
field [city] equals New York, I wish to include the excluded streets".
That's what John provided.

What do you mean by "There are numerous cities"? Do you mean that there are
numerous cities that have "New York, New York" for their address? Is there
something in your data that indicates which records are Manhattan addresses?

There are numerous cities since the local practice for the post office is to
refer to the neighborhood in the City section, i.e. Jamaica, New York, but
Manhattan is only referred to as New York, New York. I appreciate Mr.
Spencer's help, but the assumption of only one city is not applicable.

If any reader has a suggestion to exclude all apartments except in
Manhattan, , same would be appreciated.

Is this the only criteria you have applied in the query?

If so and if you are using the grid, just go to the next line in the criteria
section and enter "New York" under the City field.

Field: City
Criteria (Line 1): <Leave this blank>
Criteria (Line 2): "New York"

If you have other criteria on the first line (besides the Street criteria), you
will need to repeat the criteria in criteria line 2.

If you were entering this directly into the SQL text window, you would have
something like

WHERE City = "New York"
OR (Street Not Like "* Unit *"
And Street Not Like "* apt. *"
And Not Street Like "* apt *"
And Street Not Like "* apartment *"
And Street Not Like "* [#] *")

By the way # is a wild card character meaning any number character. So Street
Not Like "* # *" translates to any address with a single number surrounded by
spaces. So "No. 1 South Main" would be excluded under this criterion.

I think what you want is
Street Not Like "* [#] *"

That will exclude street addresses with a # character surrounded by spaces.

In field [street], I have inserted the following:

Not Like "* Unit *" And Not Like "* apt. *" And Not Like "* apt *" And Not
Like "* apartment *" And Not Like "* # *"

However, when field [city] equals New York, I wish to include the excluded
streets. The reason being no one New York (manhattan) lives in a house,
everyone has an apartment number.

What is the syntax within a query. I use access 2000. I don't know sql,
basic or other programming language so help on a simplistic basis would be
appreciated. Thanks in advance to any readers who might respond.