Query

[Clyde Thorne]

In my Query, instead of showing the actual information, I would like the
sheet to show "Yes" or "No" to show whether the information is available.
Is this possible? If so, how do I do it?

 

[Clyde Thorne]

I have a real estate database. In one are the Parcel Numbers of properties.
In the other column, Land Zoning, it shows the code *RZ if the land is
within a Renaissance Zone. What I want to do, if the Land Zone has the code
*RZ in it, I would like the column to read "Yes" or "No", meaning that it is
in or not in the Renaissance Zone.

MI14444444 RRZ
MI15000000
MI58899999 CRZ
MI99999999

The above is an example of how I separated the zoning. If it easier, I can
add all of the zoning codes.

Thanks, Clyde

 

[John Vinson]

I have a real estate database. In one are the Parcel Numbers of properties.
In the other column, Land Zoning, it shows the code *RZ if the land is
within a Renaissance Zone. What I want to do, if the Land Zone has the code
*RZ in it, I would like the column to read "Yes" or "No", meaning that it is
in or not in the Renaissance Zone.

MI14444444 RRZ
MI15000000
MI58899999 CRZ
MI99999999

Use a calculated field:

InRZ: IIF(InStr([Land Zoning] & " ", "RZ") = 2, "Yes", "No")

This assumes that there are other non-NULL values of Land Zoning which
don't contain RZ in the 2nd and 3rd bytes. I'm appending a blank to
Land Zoning to avoid errors if it's NULL.

John W. Vinson[MVP]

 

[Clyde Thorne]

In the Query Design, using Build, I typed in the string you wrote. I am not
that literate in using strings. I keep getting an error that there is a
invalid parentheses. In Query Design, I left click in the Criteria box
under the Field "Land Zoning". Then I open the design window and type in
the string. Am I doing this right? Thanks.


Clyde

I have a real estate database. In one are the Parcel Numbers of
properties.
In the other column, Land Zoning, it shows the code *RZ if the land is
within a Renaissance Zone. What I want to do, if the Land Zone has the
code
*RZ in it, I would like the column to read "Yes" or "No", meaning that it
is
in or not in the Renaissance Zone.

MI14444444 RRZ
MI15000000
MI58899999 CRZ
MI99999999

Use a calculated field:

InRZ: IIF(InStr([Land Zoning] & " ", "RZ") = 2, "Yes", "No")

This assumes that there are other non-NULL values of Land Zoning which
don't contain RZ in the 2nd and 3rd bytes. I'm appending a blank to
Land Zoning to avoid errors if it's NULL.

John W. Vinson[MVP]

 

[Clyde Thorne]

Scratch that. I realize what I was doing wrong. Just one more thing if you
please. When the data comes up, "YES" comes up for land code CRZ or RRZ,
but for those code 14RZ or 20RZ, I am getting the word "NO". What can I do
to correct this. Thanks.

Clyde

I have a real estate database. In one are the Parcel Numbers of
properties.
In the other column, Land Zoning, it shows the code *RZ if the land is
within a Renaissance Zone. What I want to do, if the Land Zone has the
code
*RZ in it, I would like the column to read "Yes" or "No", meaning that it
is
in or not in the Renaissance Zone.

MI14444444 RRZ
MI15000000
MI58899999 CRZ
MI99999999

Use a calculated field:

InRZ: IIF(InStr([Land Zoning] & " ", "RZ") = 2, "Yes", "No")

This assumes that there are other non-NULL values of Land Zoning which
don't contain RZ in the 2nd and 3rd bytes. I'm appending a blank to
Land Zoning to avoid errors if it's NULL.

John W. Vinson[MVP]

 

[John Vinson]

Scratch that. I realize what I was doing wrong. Just one more thing if you
please. When the data comes up, "YES" comes up for land code CRZ or RRZ,
but for those code 14RZ or 20RZ, I am getting the word "NO". What can I do
to correct this. Thanks.

To show YES for values with RZ *anywhere* in the string after the
first byte just change = to >=:

InRZ: IIF(InStr([Land Zoning] & " ", "RZ") >= 2, "Yes", "No")

John W. Vinson[MVP]

 

[Clyde Thorne]

Thank you very much. Everything works perfectly.

Clyde

Scratch that. I realize what I was doing wrong. Just one more thing if
you
please. When the data comes up, "YES" comes up for land code CRZ or RRZ,
but for those code 14RZ or 20RZ, I am getting the word "NO". What can I
do
to correct this. Thanks.

To show YES for values with RZ *anywhere* in the string after the
first byte just change = to >=:

InRZ: IIF(InStr([Land Zoning] & " ", "RZ") >= 2, "Yes", "No")

John W. Vinson[MVP]