Query not showing a record, but the table is

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Not sure whether this question belongs in Queries or Forms.

I have a Query By Form set up to search a database of contacts. There are
two instances of the name "Welsh" in the Contacts table, but only one of them
appears in the Search Results form after using the Search form to query the
table. The same thing is true of the Data Entry form. The reason for this is
that the second Welsh does not appear in the query that is the record source
for the Data Entry form. I'm sure the same thing is happening with the search
query.

The search performs as expected in my development database. I tried
importing both forms and underlying queries and the into the front end, but
still no luck.

Any ideas?
 
Is there a trailing space on the end of one of them? ie. "Welsh "

Cheers,
Jason Lepack
 
Are there any differences between the two recods when you look at them in the
table?

What is the query used as the datasource for your form?

Dale
 
I have checked both records and there are no major differences -- they each
have the essentials. (And in my Dev database, the queries work.)

Here is the SQL for the data entry form:
SELECT tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order, tbl_Contacts.LastName, tbl_Contacts.FirstName,
[FirstName] & " " & [LastName] AS FullName, tbl_Contacts.ContactType,
tbl_Contacts.Phone, tbl_Contacts.Ext, tbl_Contacts.Fax, tbl_Contacts.Email,
tbl_Contacts.Address1, tbl_Contacts.Address2, tbl_Contacts.Address3,
tbl_Contacts.Street, tbl_Contacts.City, tbl_Contacts.StateAbr,
tbl_Contacts.Zip, tbl_Contacts.ListServe, tbl_Contacts.Web,
tbl_Contacts.QUICKWrkgrp, tbl_Contacts.DataCenter, tbl_Contacts.CreateDate,
tbl_Contacts.CreatedBy, tbl_Contacts.RevDate, tbl_Contacts.RevBy,
tbl_Contacts.ContactID, tbl_Contacts.AddressType
FROM (tbl_Contacts INNER JOIN tbl_List_Contact_Type ON
tbl_Contacts.ContactType = tbl_List_Contact_Type.ContactType) INNER JOIN
tbl_List_States_Contacts ON tbl_Contacts.State =
tbl_List_States_Contacts.State
ORDER BY tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order;

The SQL for the Search query is a doozy because it's set up for the
possibility of null values in the Search request form, e.g., Last Name, but
no State Name.
It is too long to post (I received an error message.)
 
Susan,

Change your Inner Joins to LEFT JOINs (include all from tbl_Contacts) and
see what happens. My guess is that you have a NULL value in either the
ContactType or the State field of the contacts table, or one of these values
is missing from the related Contact_Type or List_States tables. If either
of these is the case, then Access cannot create the perfect match required
by the INNER JOIN, and will drop that record.

HTH
Dale

Susan L said:
I have checked both records and there are no major differences -- they each
have the essentials. (And in my Dev database, the queries work.)

Here is the SQL for the data entry form:
SELECT tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order, tbl_Contacts.LastName,
tbl_Contacts.FirstName,
[FirstName] & " " & [LastName] AS FullName, tbl_Contacts.ContactType,
tbl_Contacts.Phone, tbl_Contacts.Ext, tbl_Contacts.Fax,
tbl_Contacts.Email,
tbl_Contacts.Address1, tbl_Contacts.Address2, tbl_Contacts.Address3,
tbl_Contacts.Street, tbl_Contacts.City, tbl_Contacts.StateAbr,
tbl_Contacts.Zip, tbl_Contacts.ListServe, tbl_Contacts.Web,
tbl_Contacts.QUICKWrkgrp, tbl_Contacts.DataCenter,
tbl_Contacts.CreateDate,
tbl_Contacts.CreatedBy, tbl_Contacts.RevDate, tbl_Contacts.RevBy,
tbl_Contacts.ContactID, tbl_Contacts.AddressType
FROM (tbl_Contacts INNER JOIN tbl_List_Contact_Type ON
tbl_Contacts.ContactType = tbl_List_Contact_Type.ContactType) INNER JOIN
tbl_List_States_Contacts ON tbl_Contacts.State =
tbl_List_States_Contacts.State
ORDER BY tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order;

The SQL for the Search query is a doozy because it's set up for the
possibility of null values in the Search request form, e.g., Last Name,
but
no State Name.
It is too long to post (I received an error message.)




--
susan


Dale Fye said:
Are there any differences between the two recods when you look at them in
the
table?

What is the query used as the datasource for your form?

Dale
Click to expand...
Click to expand...
 
You are "right on the money." A contact type "Other" was missing from the
tbl_List_Contact_Types; therefore, the other Welsh, who is an Other, did not
show up. I changed the join type to Left and Voila!

Thanks so much for your help. And for opening my eyes a bit wider as to what
the type of join does. I haven't been giving as much thought as I should to
constructing them.
--
susan


Dale Fye said:
Susan,

Change your Inner Joins to LEFT JOINs (include all from tbl_Contacts) and
see what happens. My guess is that you have a NULL value in either the
ContactType or the State field of the contacts table, or one of these values
is missing from the related Contact_Type or List_States tables. If either
of these is the case, then Access cannot create the perfect match required
by the INNER JOIN, and will drop that record.

HTH
Dale

Susan L said:
I have checked both records and there are no major differences -- they each
have the essentials. (And in my Dev database, the queries work.)

Here is the SQL for the data entry form:
SELECT tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order, tbl_Contacts.LastName,
tbl_Contacts.FirstName,
[FirstName] & " " & [LastName] AS FullName, tbl_Contacts.ContactType,
tbl_Contacts.Phone, tbl_Contacts.Ext, tbl_Contacts.Fax,
tbl_Contacts.Email,
tbl_Contacts.Address1, tbl_Contacts.Address2, tbl_Contacts.Address3,
tbl_Contacts.Street, tbl_Contacts.City, tbl_Contacts.StateAbr,
tbl_Contacts.Zip, tbl_Contacts.ListServe, tbl_Contacts.Web,
tbl_Contacts.QUICKWrkgrp, tbl_Contacts.DataCenter,
tbl_Contacts.CreateDate,
tbl_Contacts.CreatedBy, tbl_Contacts.RevDate, tbl_Contacts.RevBy,
tbl_Contacts.ContactID, tbl_Contacts.AddressType
FROM (tbl_Contacts INNER JOIN tbl_List_Contact_Type ON
tbl_Contacts.ContactType = tbl_List_Contact_Type.ContactType) INNER JOIN
tbl_List_States_Contacts ON tbl_Contacts.State =
tbl_List_States_Contacts.State
ORDER BY tbl_List_States_Contacts.StateID, tbl_Contacts.State,
tbl_List_Contact_Type.Order;

The SQL for the Search query is a doozy because it's set up for the
possibility of null values in the Search request form, e.g., Last Name,
but
no State Name.
It is too long to post (I received an error message.)




--
susan


Dale Fye said:
Are there any differences between the two recods when you look at them in
the
table?

What is the query used as the datasource for your form?

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I checked. No there isn't. (I was hoping so.) None before either.
--
susan


:

Is there a trailing space on the end of one of them? ie. "Welsh "

Cheers,
Jason Lepack

Not sure whether this question belongs in Queries or Forms.

I have a Query By Form set up to search a database of contacts.
There are
two instances of the name "Welsh" in the Contacts table, but only
one of them
appears in the Search Results form after using the Search form to
query the
table. The same thing is true of the Data Entry form. The reason
for this is
that the second Welsh does not appear in the query that is the
record source
for the Data Entry form. I'm sure the same thing is happening with
the search
query.

The search performs as expected in my development database. I tried
importing both forms and underlying queries and the into the front
end, but
still no luck.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top