How to modify qryOwnerList to not show John Doe record?

[MLH]

On the outside chance that there IS some poor sucker out
there named John Doe, Jane Doe or Richard Roe, I'm allowing
them to be entered in tblOwners. I need a work-a-round though.
Help me out here.

I have a qryOwnerList containing no calculated fields - just lists
tblOwner records by last name first first name middle name last -
you know.. frmEditOwners uses qryOwnerList, sorting owners in
a manageable fashion. I do not wish for the John Doe record to
appear in the list unless the poor guy is really named John Doe.

So, I'm modifying qryOwnerList from

SELECT tblOwners.OwnerID, [OwnerFName] & " " & [OwnerLName] AS Owner,
[OwnerAddr] & " " & [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS Address, tblOwners.OwnerPhone, tblOwners.OwnerLName,
tblOwners.OwnerFName, tblOwners.OwnerAddr, tblOwners.OwnerPObox,
tblOwners.OwnerCity, tblOwners.OwnerState, tblOwners.OwnerZip,
tblOwners.OwnerSSN, [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS OwnerCSZ, tblOwners.Corporation
FROM tblOwners
ORDER BY tblOwners.OwnerLName, tblOwners.OwnerFName;
to

SELECT tblOwners.OwnerID, [OwnerFName] & " " & [OwnerLName] AS Owner,
[OwnerAddr] & " " & [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS Address, tblOwners.OwnerPhone, tblOwners.OwnerLName,
tblOwners.OwnerFName, tblOwners.OwnerAddr, tblOwners.OwnerPObox,
tblOwners.OwnerCity, tblOwners.OwnerState, tblOwners.OwnerZip,
tblOwners.OwnerSSN, [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS OwnerCSZ, tblOwners.Corporation, [OwnerFName] &
[OwnerLName] & [OwnerState] & [OwnerZip] AS asdf
FROM tblOwners
WHERE ((Not ([OwnerFName] & [OwnerLName] & [OwnerState] &
[OwnerZip])="JohnDoeXXxxxx"))
ORDER BY tblOwners.OwnerLName, tblOwners.OwnerFName;
and I'm wondering if there is, perhaps a more elegant way. The
app is useful only in the US. Here, we have no state abbreviated
XX or zip code of xxxxx. So, the SQL does the trick

[Thelma Lubkin]

MLH <CR**@NorthState.net> wrote:
<snip>
: I have a qryOwnerList containing no calculated fields - just lists
: tblOwner records by last name first first name middle name last -
: you know.. <snip>

: So, I'm modifying qryOwnerList from

: SELECT tblOwners.OwnerID, ...<snip>

: to

: SELECT tblOwners.OwnerID, ...<snip>
: WHERE ((Not ([OwnerFName] & [OwnerLName] & [OwnerState] &
: [OwnerZip])="JohnDoeXXxxxx"))
: ORDER BY tblOwners.OwnerLName, tblOwners.OwnerFName;
: and I'm wondering if there is, perhaps a more elegant way. The
: app is useful only in the US. Here, we have no state abbreviated
: XX or zip code of xxxxx. So, the SQL does the trick
Shorter, but not elegant.
If you've actually assigned him to state 'XX', then that's all
your WHERE needs to test for
WHERE ( Not ([OwnerState] = "XX"))
...so you're not restricting state to the 50 valid abbreviations
--thelma

[Randy Harris]

You haven't supplied sufficient information. What you are proposing makes
little sense.

Why is there an invalid "John Doe" record in your table in the first place?
Are you saying that you also have an invalid "Jane Doe" record? How many
other invalid records?

Doesn't your table have a primary key? It should! Use it!

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"MLH" <CR**@NorthState.net> wrote in message
news:qj********************************@4ax.com...

On the outside chance that there IS some poor sucker out
there named John Doe, Jane Doe or Richard Roe, I'm allowing
them to be entered in tblOwners. I need a work-a-round though.
Help me out here.

I have a qryOwnerList containing no calculated fields - just lists
tblOwner records by last name first first name middle name last -
you know.. frmEditOwners uses qryOwnerList, sorting owners in
a manageable fashion. I do not wish for the John Doe record to
appear in the list unless the poor guy is really named John Doe.

So, I'm modifying qryOwnerList from

SELECT tblOwners.OwnerID, [OwnerFName] & " " & [OwnerLName] AS Owner,
[OwnerAddr] & " " & [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS Address, tblOwners.OwnerPhone, tblOwners.OwnerLName,
tblOwners.OwnerFName, tblOwners.OwnerAddr, tblOwners.OwnerPObox,
tblOwners.OwnerCity, tblOwners.OwnerState, tblOwners.OwnerZip,
tblOwners.OwnerSSN, [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS OwnerCSZ, tblOwners.Corporation
FROM tblOwners
ORDER BY tblOwners.OwnerLName, tblOwners.OwnerFName;
to

SELECT tblOwners.OwnerID, [OwnerFName] & " " & [OwnerLName] AS Owner,
[OwnerAddr] & " " & [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS Address, tblOwners.OwnerPhone, tblOwners.OwnerLName,
tblOwners.OwnerFName, tblOwners.OwnerAddr, tblOwners.OwnerPObox,
tblOwners.OwnerCity, tblOwners.OwnerState, tblOwners.OwnerZip,
tblOwners.OwnerSSN, [OwnerCity] & ", " & [OwnerState] & " " &
[OwnerZip] AS OwnerCSZ, tblOwners.Corporation, [OwnerFName] &
[OwnerLName] & [OwnerState] & [OwnerZip] AS asdf
FROM tblOwners
WHERE ((Not ([OwnerFName] & [OwnerLName] & [OwnerState] &
[OwnerZip])="JohnDoeXXxxxx"))
ORDER BY tblOwners.OwnerLName, tblOwners.OwnerFName;
and I'm wondering if there is, perhaps a more elegant way. The
app is useful only in the US. Here, we have no state abbreviated
XX or zip code of xxxxx. So, the SQL does the trick

[MLH]

Yes, I guess you're right, Thelma. I guess you're right.

What I have is a one-to-many relationship between
tblOwners and tblVehicles - an owner could own any
number of vehicles in tblVehicles. Occasionally, we
acquire a vehicle for whom there is no owner that can
be determined. This is the exception, rather than the
rule. My reason for creating 1 single John Doe record
in the owners table is that having that record there
eliminates the need for a lot of other red tape throughout
my application. It hurts nothing, really. And I never had
to bother with Nz or checking whether the OwnerID field
was Null for any given vehicle before attempting to do
something with the value.

The John Doe record is not created until a vehicle w/
no owner is acquired. And, of course, it never has to be
added again - ever. And no, I have no defined relationship
from a tblStates to the OwnerState field in tblOwners. For
maintaining referential integrity, I do DLookUps of the state
abbreviations in a zip code table after the user completes
the zip field (at the form level) and I offer no direct table
access otherwise. The integrity of that smidgen of data
just doesn't make or break the app in any significant way.
If a guy wants to enter ZP for some state abbrevation after
I've done a dlookup & filled in the correct 2-char state code -
well, that's his loss. Hasn't happened yet in about 37000
records spread over 70 separate runtime installations that
I know of. So I'm not losing much sleep over it.

Thx for the pointer.

Shorter, but not elegant.
If you've actually assigned him to state 'XX', then that's all
your WHERE needs to test for
WHERE ( Not ([OwnerState] = "XX"))
...so you're not restricting state to the 50 valid abbreviations
--thelma

[MLH]

>You haven't supplied sufficient information. What you are proposing makes

little sense. Sorry, Randy. It happens.
I have this table (tblOwners) and it consists mostly of valid names of
real persons who own vehicles (stored separately in tblVehicles). Once
in a while, we add a rec to the vehicles table that has no owner.
Although its perfectly acceptable to have a vehicle record with no
related owner record, we have introduced John Doe - a nonexistent
person who has a name. This works out quite well for the legal reports
that we auto-fill from our stored data. In most courts, John Doe is
the legal name in which an unknown owner (one whose records
cannot be obtained from the DMV) is sued under. The courts, of course,
never expect John to have an address - works perfectly for us.

Why is there an invalid "John Doe" record in your table in the first place?
Are you saying that you also have an invalid "Jane Doe" record? How many
other invalid records? No Jane Doe's. Just John. I was just throwing in Jane Doe and Richard
Roe for emphasis.
Doesn't your table have a primary key? It should! Use it!