Query with Inner Join fails when using SQL Server BE

[thirt]

I just upsized my current Access BE to SQL Server. I'm cleaning up the Access front end and came accross subforms not displaying data correctly. I'm looking at the underlying query and found out that the inner join is only showing matched records, and not all the records on the left as it normally does.

The main query:
SELECT qselPartyContactPurposesTelcoPeople.fldContactMechanismPurposeIndexNo,
qselPartyContactPurposesTelcoPeople.fldContactMechanismPurpose, qselPartyContactsTelcoPeople.*
FROM qselPartyContactPurposesTelcoPeople LEFT JOIN
qselPartyContactsTelcoPeople ON
qselPartyContactPurposesTelcoPeople.fldContactMechanismPurposeIndexNo = qselPartyContactsTelcoPeople.fldContactMechanismPurposeIndexNo;

Note that the inner join links the two queries:
Query:qselPartyContactPurposesTelcoPeople
SELECT tlkpContactMechanismPurposes.*
FROM tlkpContactMechanismPurposes INNER JOIN tblFormsXContactMechPurposes ON tlkpContactMechanismPurposes.fldContactMechanismPurposeIndexNo = tblFormsXContactMechPurposes.fldContactMechanismPurposeIndexNo
WHERE (((tblFormsXContactMechPurposes.fldForm)='frmPeoplefsfrPartyContactsTelcoPeople'))
ORDER BY tlkpContactMechanismPurposes.fldContactMechanismPurpose;

This query just comes back with 5 possible phone types. I want to display all 5 options, reguardless if there is matching data. So I inner join to:

Query: qselPartyContactsTelcoPeople
SELECT tblPartyContactMechanisms.*
FROM tblPartyContactMechanisms
WHERE (((tblPartyContactMechanisms.fldPartyIndexNo)=[Forms]![frmPeople]![txtKeyField]));

The results of this query is just one record.

Now here is were it gets wierd. If I take the WHERE statement above and had code the txtkeyfield value to say 1, the main query works perfectly and displays all 5 records, including the match. But when the WHERE refers to the valid form field, the main query will only return 1 record, the record with a match.

When I run the WHERE query by itself, I get the same recordset if WHERE is using the form or hard coded.

Any ideas?

 

[SiJP]

I'd probably look at datatypes.. you've explicitly entered a 1 in as criteria and the query works.. because it knows you are using the number datatype.. but what about your txtKeyField.. what datatype is this?

------------------------
Hit any User to continue

 

[thirt]

Well this is interseting. I went into the properties of the main query and change the recordtype from "Dynaset" default to "Dynaset (Inconsistent Updates)" and the bloody thing now works.

I don't get it, but it works.

 

[cmmrfrds]

The inconsistent updates must be a coincidence, since that has no effect on the query bringing back the data.

On an inner join, it is by definition only matching records.

On a left join, if you apply a where clause to a field on the left side table, you will only get matching records since that negates the effect of the left join. In that case, you need to explicitly include asking for the field being null. Add an "OR fieldvalue is null" to the where clause to pick up those records in the query.